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