Value Error Querie

[Woblypegs]

Hi Folks, I have a spreadsheet I'm working on and in one column I have the formula

=IF(([@[Time (Hrs)]]*[@[Rate (Hrs)]])-([@[$ Paid]]+[@[Admin Fee]])=0,"✓",([@[Time (Hrs)]]*[@[Rate (Hrs)]])-([@[$ Paid]]+[@[Admin Fee]])).

right down the entire column it gives me the correct answers in each cell except one, where it comes up with a value error. The data is the same it's all formatted correctly and the same, but, in this one cell it gives me that value error. However if I change the formula to,

=IF((SUM([@[Time (Hrs)]])*SUM([@[Rate (Hrs)]]))-(SUM([@[$ Paid]])+SUM([@[Admin Fee]]))=0,"✓",(SUM([@[Time (Hrs)]])*SUM([@[Rate (Hrs)]]))-(SUM([@[$ Paid]])+SUM([@[Admin Fee]])))

it fixes this problem. Now these are individual cells there is no merging of cells so I don't understand why this is happening. If anyone can enlighten me it would be awesome because this sort of **** does my head in.

I know I could use

=IFERROR(IF(([@[Time (Hrs)]]*[@[Rate (Hrs)]])-([@[$ Paid]]+[@[Admin Fee]])=0,"✓",([@[Time (Hrs)]]*[@[Rate (Hrs)]])-([@[$ Paid]]+[@[Admin Fee]]))."")

But I don't like using that and it says in any tutorials that I have seen that this is not advisable.

Many Thanks
Wobly

 

[Joe4]

Break up your formula into the smaller bits, to see which part exactly is causing the error. Then you can focus on that part.
Check the data for anything out or the ordinary on that row, i.e. numbers entered as text, extra characters, etc.

 

[Woblypegs]

All the data is correct and formatted correctly, The formula is entered correctly. I've checked and rechecked, it makes no sense.

 

[Woblypegs]

Yet if I use the second formula it works fine and I don't understand why.

Wobly

 

[Yongle]

@Joe4 provided one method to find the problem value
- did you try that ?

Repeating the question is not moving you forward
(Visible or not) Excel thinks that there is a bad value in that row in one of 4 columns [ Time (Hrs) \ Rate (Hrs) \ $ Paid \ Admin Fee ]
One of the values is non-numeric

Something else to try ...
Add a temporary column to the right of the table
In the problem row enter this formula
="Time:"&ISNONTEXT([@[Time (Hrs)]])&" Rate:"&ISNONTEXT([@[Rate (Hrs)]])&" Paid:"&ISNONTEXT([@[$ Paid]])&" Admin:"&ISNONTEXT([@[Admin Fee]])
which should return
Time:TRUE Rate:TRUE Paid:TRUE Admin:TRUE
If FALSE is returned, it tells you which value is in error

FIX - should fix the problem by replacing an invalid value with something Excel is happy to accept
For the 4 cells in problem row in columns "Time (Hrs)", " Rate (Hrs)", "$ Paid " & "Admin Fee " ...
... COPY the cell 2 rows above (ie a cell in a "grey" row)
... PASTE into problem row (simple paste NOT paste values)
... use F2 to amend the cell value to the correct value

 

[Joe4]

It could be that one of those blank columns is not really blank. It could have a space or some special character in it.
If you try what I suggested, it should be able to help you zero in on exactly where the problem occurs.

 

[Woblypegs]

I did, nothing was obvious. And your formula result comes out as all true.
"(Visible or not) Excel thinks that there is a bad value in that row in one of 4 columns [ Time (Hrs) \ Rate (Hrs) \ $ Paid \ Admin Fee ] "
Yes this I know, the four columns are formatted correctly as far as I can see, I've re formatted them (The entire column), I reentered the data and got the same result.
Anyway thanks very much for your help.

Wobly

 

[Joe4]

Since nothing is obviously standing out, it is very difficult to figure out what might be going on without having access to the data.
Is the worksheet something that you can upload to a file share site for us to download and see?
If so, upload to some file sharing site (like Dropbox), and provide a link to it in this thread.

 

[Woblypegs]

The actual worksheet that I'm working on can't be uploaded. The privacy laws I'd be breaking with the data that it contains would be unacceptable. Thanks for your help, but I'm going to rebuild that portion of the table from scratch to see if that fixes it. It can wait until tomorrow though. Will keep you posted.

Again Thanks
Wobly

 

[Yongle]

another useful formula to see what type of data is in a cell
=TYPE(A1)

Your problem formula requires that all 4 cells contain a number, so each one should be a 1

TYPE returns these values
1 Number
2 Text
4 Logical value
16 Error value
64 Array

Given what you have already told us, the above will not yield anything any different, but it may be useful for something else