| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
| LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
| HELP with dreaded formulas... | Excel Worksheet Functions | |||
| Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
| Avoiding the dreaded #div/0 error | Excel Worksheet Functions | |||
Hybrid Mode
