Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtract 2 cells and get blank instead of 0.0
I have templates set up to subtract cell A1-B1 and a colume that shows me the
amount of change. The default cell value even if A1 and B1 are blank is 0.0. On a different sheet I have the compiled data that shows me the average and std dev of all the runs. For the grand total I am using the function: =AVERAGE(IF(ISNUMBER(H3:H202),H3:H202)) (used the CSE) This works fine but this is a template and the reason for this function is to not have to delete unused rows. When I have unused rows it is still using the 0.0 because it is number. How do I change the subtraction so it doesn't give me 0.0 until data is entered into cell A1 and B1? Example is =a1-b1 returnes 0.0, I want it to be blank. Thank you for your help. Tim |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtract 2 cells and get blank instead of 0.0
=IF(A1-B1=0,"",A1-B1)
but be careful of rounding. -- Gary''s Student - gsnu200803 "tpeter" wrote: I have templates set up to subtract cell A1-B1 and a colume that shows me the amount of change. The default cell value even if A1 and B1 are blank is 0.0. On a different sheet I have the compiled data that shows me the average and std dev of all the runs. For the grand total I am using the function: =AVERAGE(IF(ISNUMBER(H3:H202),H3:H202)) (used the CSE) This works fine but this is a template and the reason for this function is to not have to delete unused rows. When I have unused rows it is still using the 0.0 because it is number. How do I change the subtraction so it doesn't give me 0.0 until data is entered into cell A1 and B1? Example is =a1-b1 returnes 0.0, I want it to be blank. Thank you for your help. Tim |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtract 2 cells and get blank instead of 0.0
I figured it out, just changed core values to an if statement
=if(a1="","",a1-b1) "tpeter" wrote: I have templates set up to subtract cell A1-B1 and a colume that shows me the amount of change. The default cell value even if A1 and B1 are blank is 0.0. On a different sheet I have the compiled data that shows me the average and std dev of all the runs. For the grand total I am using the function: =AVERAGE(IF(ISNUMBER(H3:H202),H3:H202)) (used the CSE) This works fine but this is a template and the reason for this function is to not have to delete unused rows. When I have unused rows it is still using the 0.0 because it is number. How do I change the subtraction so it doesn't give me 0.0 until data is entered into cell A1 and B1? Example is =a1-b1 returnes 0.0, I want it to be blank. Thank you for your help. Tim |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
subtract 2 cells and get blank instead of 0.0
If your values are positive only...
=AVERAGE(IF((H3:H2030),H3:H203)) (Use CSE) "tpeter" wrote: I have templates set up to subtract cell A1-B1 and a colume that shows me the amount of change. The default cell value even if A1 and B1 are blank is 0.0. On a different sheet I have the compiled data that shows me the average and std dev of all the runs. For the grand total I am using the function: =AVERAGE(IF(ISNUMBER(H3:H202),H3:H202)) (used the CSE) This works fine but this is a template and the reason for this function is to not have to delete unused rows. When I have unused rows it is still using the 0.0 because it is number. How do I change the subtraction so it doesn't give me 0.0 until data is entered into cell A1 and B1? Example is =a1-b1 returnes 0.0, I want it to be blank. Thank you for your help. Tim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maximum Number of Blank Cells between Non Blank Cells in a Range | Excel Worksheet Functions | |||
Subtract or leave blank in cell | Excel Worksheet Functions | |||
Subtract or leave blank in cell | Excel Worksheet Functions | |||
How can I subtract cells in a row | New Users to Excel | |||
how to add or subtract from two cells into one | Excel Worksheet Functions |