Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE! in cell
My formula is =SUM(B8:C8)*G8 where either B8 or C8 will always =0. How do I
not get this value" #VALUE!" when the cells are not in use? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE! in cell
Perhaps you have a formula in B8 or C8 that returns "0" rather than 0.
Note the first of these is a text value and this will give you the #VALUE error if you try to use it with arithmetic. Or, it could be that a formula in G8 is returning a number enclosed within quotes, and again this is a text value and will give that error. Only you know what formulae you have, as you have not shared those with us. Note that you don't really need the SUM in this case - you could write your formula as: =(B8+C8)*G8 Hope this helps. Pete On Dec 19, 1:38*am, datub wrote: My formula is =SUM(B8:C8)*G8 where either B8 or C8 will always =0. How do I not get this value" #VALUE!" when the cells are not in use? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE! in cell
Empty cells won't cause a #VALUE error with this formula. You should only get
a #VALUE error if any of the cells (B8, C8, or G8) has a formula that evaluates to #VALUE, or if G8 contains text (a non-numerical value). Hope this helps, Hutch "datub" wrote: My formula is =SUM(B8:C8)*G8 where either B8 or C8 will always =0. How do I not get this value" #VALUE!" when the cells are not in use? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE! in cell
Should not return error unless there are text values in those cells. Try
=IF(ISERROR((B8+C8)*G8),"",(B8+C8)*G8) -- Jacob "datub" wrote: My formula is =SUM(B8:C8)*G8 where either B8 or C8 will always =0. How do I not get this value" #VALUE!" when the cells are not in use? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE! in cell
You will get the "#VALUE!" Error when G8 cell is having any Text String. The
=SUM(B8:C8) will not get the "#VALUE!" error. -------------------- (Ms-Exl-Learner) -------------------- "datub" wrote: My formula is =SUM(B8:C8)*G8 where either B8 or C8 will always =0. How do I not get this value" #VALUE!" when the cells are not in use? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
#VALUE! in cell
Remember, Pete, that the difference between
=SUM(B8:C8)*G8 and =(B8+C8)*G8 is that the former will *not* return #VALUE! if B8 or C8 is text (such as "0") but the latter *will* return a #VALUE! error in such circumstances. Either will return #VALUE! if G8 is text. The best solution would be to avoid putting text values into the formula, but one solution would (strangely) be =SUM(B8:C8)*SUM(G8), which would treat any text values as zero. [We certainly would't want to see =SUM(B8+C8)*G8, or =SUM(B8+C8)*SUM(G8), because either of those would return #VALUE! if B8 or C8 is text, and the SUM function does nothing useful round B8+C8.] A healthier solution would be =IF(COUNT(B8,C8,G8)=3,(B8+C8)*G8,"") -- David Biddulph "Pete_UK" wrote in message ... Perhaps you have a formula in B8 or C8 that returns "0" rather than 0. Note the first of these is a text value and this will give you the #VALUE error if you try to use it with arithmetic. Or, it could be that a formula in G8 is returning a number enclosed within quotes, and again this is a text value and will give that error. Only you know what formulae you have, as you have not shared those with us. Note that you don't really need the SUM in this case - you could write your formula as: =(B8+C8)*G8 Hope this helps. Pete On Dec 19, 1:38 am, datub wrote: My formula is =SUM(B8:C8)*G8 where either B8 or C8 will always =0. How do I not get this value" #VALUE!" when the cells are not in use? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Options | Excel Discussion (Misc queries) | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing | Excel Discussion (Misc queries) | |||
Populate a cell if values in cell 1 and cell 2 match cell 3 and 4 | Excel Worksheet Functions | |||
How can I copy a value from a cell and paste it into another cell while adding it to the previous value in that cell | Excel Worksheet Functions | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) |