Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with formula
Hi, can anyone tell me how to get:
=ROUND(D23*C$42,3) answer appears in cell E23 to run when some cells are blank please? I have other formula doing similar stuff, but are unsure if the principles are generic - cant get it to work either way, so are open to suggestions?? Ted. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with formula
whats in D23 and C42?
"Ted" wrote in message ... Hi, can anyone tell me how to get: =ROUND(D23*C$42,3) answer appears in cell E23 to run when some cells are blank please? I have other formula doing similar stuff, but are unsure if the principles are generic - cant get it to work either way, so are open to suggestions?? Ted. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with formula
numbers - just using the "general" function
"Mike G" wrote: whats in D23 and C42? "Ted" wrote in message ... Hi, can anyone tell me how to get: =ROUND(D23*C$42,3) answer appears in cell E23 to run when some cells are blank please? I have other formula doing similar stuff, but are unsure if the principles are generic - cant get it to work either way, so are open to suggestions?? Ted. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with formula
any suggestions Mike?
"Mike G" wrote: whats in D23 and C42? "Ted" wrote in message ... Hi, can anyone tell me how to get: =ROUND(D23*C$42,3) answer appears in cell E23 to run when some cells are blank please? I have other formula doing similar stuff, but are unsure if the principles are generic - cant get it to work either way, so are open to suggestions?? Ted. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with formula
I think Mike was trying to get you to say more about what you are
wanting and why you are not happy with what you are getting. Your formula should "run" when cells are blank, but in that case will return 0. If it is returning someting else, then the cells are likely not really blank. If cells D23 and C42 contain formulas, then you need to disclose what those formulas are. Jerry Ted wrote: numbers - just using the "general" function "Mike G" wrote: whats in D23 and C42? "Ted" wrote in message ... Hi, can anyone tell me how to get: =ROUND(D23*C$42,3) answer appears in cell E23 to run when some cells are blank please? I have other formula doing similar stuff, but are unsure if the principles are generic - cant get it to work either way, so are open to suggestions?? Ted. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with formula
I was trying to get it to ignore empty cells and leave the cell where the sum
would normally display blank (as in without a zero) I've used this now: =IF(N(D24)=0,"",ROUND(D24*C48,3)) thanks for your help though, Ted. "Jerry W. Lewis" wrote: I think Mike was trying to get you to say more about what you are wanting and why you are not happy with what you are getting. Your formula should "run" when cells are blank, but in that case will return 0. If it is returning someting else, then the cells are likely not really blank. If cells D23 and C42 contain formulas, then you need to disclose what those formulas are. Jerry Ted wrote: numbers - just using the "general" function "Mike G" wrote: whats in D23 and C42? "Ted" wrote in message ... Hi, can anyone tell me how to get: =ROUND(D23*C$42,3) answer appears in cell E23 to run when some cells are blank please? I have other formula doing similar stuff, but are unsure if the principles are generic - cant get it to work either way, so are open to suggestions?? Ted. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with formula
An IF formula is the standard way to do it. Your condition could be
simplified from N(D24)=0 to D24=0 unless the issue is that it might contain text. If it is possible for D24 to contain an error that you want to ignore, or it you also need to check C48, you might want to consider COUNT(D24,C48)<2 as your condition. If all zero values are to be ignored, another possibility would be Tools|Options|View and uncheck "Zero values" Jerry Ted wrote: I was trying to get it to ignore empty cells and leave the cell where the sum would normally display blank (as in without a zero) I've used this now: =IF(N(D24)=0,"",ROUND(D24*C48,3)) thanks for your help though, Ted. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with formula
Well Jerry was right, my question was probing for what the expected inputs
are and what the expected results are. i.e. your example for num_digits = 3 meaning you want the answer rounded to 3 places. So if d23=2 and c42=10 then e23=20. Did you want 3 digit results allways?, if so format e23 for number with 3 digit rounding and then the result = 20.000. If any one of the multipliers causes the result to be 3 decimal places then the original formula (,3) would round to three places. If you do not want a "0" showing in case d23 or c42 has a "0" value, can you live with TOOLS/OPTIONS and unchecking "zero values". There are many ways to do the task, but the hard part is guessing what the expected results are with the given inputs. So, if you can do that, perhaps we can give you a solution. Mike "Ted" wrote in message ... any suggestions Mike? "Mike G" wrote: whats in D23 and C42? "Ted" wrote in message ... Hi, can anyone tell me how to get: =ROUND(D23*C$42,3) answer appears in cell E23 to run when some cells are blank please? I have other formula doing similar stuff, but are unsure if the principles are generic - cant get it to work either way, so are open to suggestions?? Ted. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
help with formula
Hi Mike, I need zeros to be recognised in other formula, so are restricted
with the 'zero values' function. I have added this formula and it seems to be doing the job: =IF(N(D24)=0,"",ROUND(D24*C$48,3)) If you notice any issues with the above, then let me know please? Thanks, Ted. "Mike G" wrote: Well Jerry was right, my question was probing for what the expected inputs are and what the expected results are. i.e. your example for num_digits = 3 meaning you want the answer rounded to 3 places. So if d23=2 and c42=10 then e23=20. Did you want 3 digit results allways?, if so format e23 for number with 3 digit rounding and then the result = 20.000. If any one of the multipliers causes the result to be 3 decimal places then the original formula (,3) would round to three places. If you do not want a "0" showing in case d23 or c42 has a "0" value, can you live with TOOLS/OPTIONS and unchecking "zero values". There are many ways to do the task, but the hard part is guessing what the expected results are with the given inputs. So, if you can do that, perhaps we can give you a solution. Mike "Ted" wrote in message ... any suggestions Mike? "Mike G" wrote: whats in D23 and C42? "Ted" wrote in message ... Hi, can anyone tell me how to get: =ROUND(D23*C$42,3) answer appears in cell E23 to run when some cells are blank please? I have other formula doing similar stuff, but are unsure if the principles are generic - cant get it to work either way, so are open to suggestions?? Ted. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula? | Excel Worksheet Functions | |||
writing a formula for a colored value | New Users to Excel | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |