If Function and the dreaded #VALUE

[Nick Wakeham]

I am setting up a spreadsheet in Excel 2007 and in cell F7 I have the
following formula : =IF(D7<>0,(SUM(D7-C7,1)/5),("")) in order that nothing
will show up in that cell until a number is put into D7 - if I don't use the
IF function I get a value of 0.2 in the cell, which I obviously don't want.
The formula in D7 is to subtract C7 from D7, add 1 and then divide the total
by 5.
However, I then need to multiply the value of F7 by 5 again, this time in G7
but neither the IF or ordinary SUM formulas will return anything but the
dreaded #VALUE.
Can someone point out were I am going wrong - I am sure it must be glaringly
obvious but I am stuck!
Thanks
Nick

 

[Rick Rothstein \(MVP - VB\)]

You will need to put something like this in G7...

=IF(F7="","",<<your formula involving 5*F7>>)

Rick

 

[Nick Wakeham]

Rick

Many thanks - sorted - thanks again

Nick

You will need to put something like this in G7...

=IF(F7="","",<<your formula involving 5*F7>>)

Rick

 

[Reitanos]

You shouldn't dread the #VALUE!, but seek to understand it:
Excel is having an issue with an operator or argument in your math.
Try dividing "tree" by 1 and you'll see it because of the obvious
operation:argument conflict.

 

[Aladin Akyurek]

F7:

=IF(N(D7),SUM(D7-C7,1)/5,"")

G7, one of:

=N(F7)*5

=IF(N(F7),F7*5,"")