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]

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.

If you cannot get it to work, copy that table out to a new file, and change all the sensitive fields (names, etc) so that you have a "dummy" file, and then upload that.

One potential place there could be problems is in your "blank" fields (columns N, O, P). You can use the LEN function to confirm they really are blank, i.e.
=LEN(N10)
=LEN(O10)
=LEN(P10)

They should all return 0. If they do not, then the field is not really blank. There is something in it that probably needs to be removed.

 

[Woblypegs]

Hi Guys I got it working, I changed the formula from "=IF(([@[Time (Hrs)]]*[@[Rate (Hrs)]])-([@[$ Paid]]+[@[Admin Fee]])=0,"✓",([@[Time (Hrs)]]*[@[Rate (Hrs)]])-([@[$ Paid]]+[@[Admin Fee]]))." to
"=IF(SUM([@[$ Amount]],[@[Admin Fee]])-[@[$ Paid]]=0,"✓",SUM([@[$ Amount]],[@[Admin Fee]])-[@[$ Paid]])". This gives me the same result in column Q without any errors. The reason I had the top formula in in the first place was because I got a value error in any cell in Column Q where no data was entered in Columns H,I,N & O, and for some reason I didn't think of this latest one until now after having a little play. It seems to be working fine now and I thank you for your time and knowledge all these tips will be very helpful in the future.

Many Thanks
Wobly

 

[Woblypegs]

"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 "


This has proven to be very useful indeed, Many Thanks.

Wobly