If Function and the dreaded #VALUE
 

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

If Function and the dreaded #VALUE
 

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

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

Rick


"Nick Wakeham" wrote in message
m...
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

If Function and the dreaded #VALUE
 

Rick

Many thanks - sorted - thanks again

Nick

"Rick Rothstein (MVP - VB)" wrote in
message ...
You will need to put something like this in G7...

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

Rick


"Nick Wakeham" wrote in message
m...
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

If Function and the dreaded #VALUE
 

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.

On Jun 3, 10:23 am, "Nick Wakeham" wrote:
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

If Function and the dreaded #VALUE
 

F7:

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

G7, one of:

=N(F7)*5

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

Nick Wakeham wrote:
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