| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiplying Empty Cells
In cell N15, I am using this formula M15*24. However sometimes M15 is blank
and when it is I get a message in cell N15 that says #VALUE!. Which throws off other formulas. I need help adding an IF statement to this formula M15*24 so that cell N15 in which the formula is located will become blank or €œ0€ when M15 does not have a value/is blank. Biff and JE McGimpsey recommended I use =IF(COUNT(K15:L15)<2,"" for a similar problem I had while subtracting dates and it has worked awesome. I tried adding it to the current formula and I came up with =IF(COUNT(M4)<2,"",(M4*24)-1) but I didnt get the same result which is the blank cell I was looking for. Any thoughts? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiplying Empty Cells
My bad I meant =IF(COUNT(M15)<2,"",(M15*24)-1)
"Workbook" wrote: In cell N15, I am using this formula M15*24. However sometimes M15 is blank and when it is I get a message in cell N15 that says #VALUE!. Which throws off other formulas. I need help adding an IF statement to this formula M15*24 so that cell N15 in which the formula is located will become blank or €œ0€ when M15 does not have a value/is blank. Biff and JE McGimpsey recommended I use =IF(COUNT(K15:L15)<2,"" for a similar problem I had while subtracting dates and it has worked awesome. I tried adding it to the current formula and I came up with =IF(COUNT(M4)<2,"",(M4*24)-1) but I didnt get the same result which is the blank cell I was looking for. Any thoughts? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiplying Empty Cells
=IF(COUNT(M4)<2,"",(M4*24)-1)
Since you're testing a single cell try it like this: =IF(COUNT(M4),(M4*24)-1,"") COUNT returns the count of numbers referenced in its arguments. If you're testing a single cell the result of COUNT can only be 1 or 0. Excel evaluates *any* number other than 0 as being TRUE. So, if COUNT =1 VLOOKUP returns the value_if_true argument which is M4*24)-1. If COUNT =0 VLOOKUP returns the value_if_false argument which is "", an empty TEXT string that makes the cell appear blank. You could also use something like this: =IF(M4<"",(M4*24)-1,"") Which means: if M4 is not equal to blank However, if M4 contained a TEXT entry (either by mistake or intentionally) then you'll get a #VALUE! error as the formula result. So, using the =IF(COUNT(M4)... version is more robust. -- Biff Microsoft Excel MVP "Workbook" wrote in message ... In cell N15, I am using this formula M15*24. However sometimes M15 is blank and when it is I get a message in cell N15 that says #VALUE!. Which throws off other formulas. I need help adding an IF statement to this formula M15*24 so that cell N15 in which the formula is located will become blank or "0" when M15 does not have a value/is blank. Biff and JE McGimpsey recommended I use =IF(COUNT(K15:L15)<2,"" for a similar problem I had while subtracting dates and it has worked awesome. I tried adding it to the current formula and I came up with =IF(COUNT(M4)<2,"",(M4*24)-1) but I didn't get the same result which is the blank cell I was looking for. Any thoughts? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiplying Empty Cells
COUNT(M4) can only be 1 (if it contains a numeric value) or 0 (if it
doesn't) so IF(COUNT(M4)=2, will always execute the FALSE branch. the problem is that your "blank" cell isn't blank - you (or someone) probably "cleared" it using the space bar, which inserts a space character (i.e., a text string). That's what's choking your formula. You could use =IF(COUNT(M25),M15*24,0) In article , Workbook wrote: In cell N15, I am using this formula M15*24. However sometimes M15 is blank and when it is I get a message in cell N15 that says #VALUE!. Which throws off other formulas. I need help adding an IF statement to this formula M15*24 so that cell N15 in which the formula is located will become blank or €œ0€ when M15 does not have a value/is blank. Biff and JE McGimpsey recommended I use =IF(COUNT(K15:L15)<2,"" for a similar problem I had while subtracting dates and it has worked awesome. I tried adding it to the current formula and I came up with =IF(COUNT(M4)<2,"",(M4*24)-1) but I didnt get the same result which is the blank cell I was looking for. Any thoughts? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiplying Empty Cells
Wow! Once again, thank you for your help. Both options are great.
"T. Valko" wrote: =IF(COUNT(M4)<2,"",(M4*24)-1) Since you're testing a single cell try it like this: =IF(COUNT(M4),(M4*24)-1,"") COUNT returns the count of numbers referenced in its arguments. If you're testing a single cell the result of COUNT can only be 1 or 0. Excel evaluates *any* number other than 0 as being TRUE. So, if COUNT =1 VLOOKUP returns the value_if_true argument which is M4*24)-1. If COUNT =0 VLOOKUP returns the value_if_false argument which is "", an empty TEXT string that makes the cell appear blank. You could also use something like this: =IF(M4<"",(M4*24)-1,"") Which means: if M4 is not equal to blank However, if M4 contained a TEXT entry (either by mistake or intentionally) then you'll get a #VALUE! error as the formula result. So, using the =IF(COUNT(M4)... version is more robust. -- Biff Microsoft Excel MVP "Workbook" wrote in message ... In cell N15, I am using this formula M15*24. However sometimes M15 is blank and when it is I get a message in cell N15 that says #VALUE!. Which throws off other formulas. I need help adding an IF statement to this formula M15*24 so that cell N15 in which the formula is located will become blank or "0" when M15 does not have a value/is blank. Biff and JE McGimpsey recommended I use =IF(COUNT(K15:L15)<2,"" for a similar problem I had while subtracting dates and it has worked awesome. I tried adding it to the current formula and I came up with =IF(COUNT(M4)<2,"",(M4*24)-1) but I didn't get the same result which is the blank cell I was looking for. Any thoughts? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiplying Empty Cells
I see you're point. Thank you for taking the time to explain that to me. I
have also appreciated your ongoing help. Thank you! "JE McGimpsey" wrote: COUNT(M4) can only be 1 (if it contains a numeric value) or 0 (if it doesn't) so IF(COUNT(M4)=2, will always execute the FALSE branch. the problem is that your "blank" cell isn't blank - you (or someone) probably "cleared" it using the space bar, which inserts a space character (i.e., a text string). That's what's choking your formula. You could use =IF(COUNT(M25),M15*24,0) In article , Workbook wrote: In cell N15, I am using this formula M15*24. However sometimes M15 is blank and when it is I get a message in cell N15 that says #VALUE!. Which throws off other formulas. I need help adding an IF statement to this formula M15*24 so that cell N15 in which the formula is located will become blank or €œ0€ when M15 does not have a value/is blank. Biff and JE McGimpsey recommended I use =IF(COUNT(K15:L15)<2,"" for a similar problem I had while subtracting dates and it has worked awesome. I tried adding it to the current formula and I came up with =IF(COUNT(M4)<2,"",(M4*24)-1) but I didn€„¢t get the same result which is the blank cell I was looking for. Any thoughts? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiplying Empty Cells
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Workbook" wrote in message ... Wow! Once again, thank you for your help. Both options are great. "T. Valko" wrote: =IF(COUNT(M4)<2,"",(M4*24)-1) Since you're testing a single cell try it like this: =IF(COUNT(M4),(M4*24)-1,"") COUNT returns the count of numbers referenced in its arguments. If you're testing a single cell the result of COUNT can only be 1 or 0. Excel evaluates *any* number other than 0 as being TRUE. So, if COUNT =1 VLOOKUP returns the value_if_true argument which is M4*24)-1. If COUNT =0 VLOOKUP returns the value_if_false argument which is "", an empty TEXT string that makes the cell appear blank. You could also use something like this: =IF(M4<"",(M4*24)-1,"") Which means: if M4 is not equal to blank However, if M4 contained a TEXT entry (either by mistake or intentionally) then you'll get a #VALUE! error as the formula result. So, using the =IF(COUNT(M4)... version is more robust. -- Biff Microsoft Excel MVP "Workbook" wrote in message ... In cell N15, I am using this formula M15*24. However sometimes M15 is blank and when it is I get a message in cell N15 that says #VALUE!. Which throws off other formulas. I need help adding an IF statement to this formula M15*24 so that cell N15 in which the formula is located will become blank or "0" when M15 does not have a value/is blank. Biff and JE McGimpsey recommended I use =IF(COUNT(K15:L15)<2,"" for a similar problem I had while subtracting dates and it has worked awesome. I tried adding it to the current formula and I came up with =IF(COUNT(M4)<2,"",(M4*24)-1) but I didn't get the same result which is the blank cell I was looking for. Any thoughts? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting text cells then multiplying by a value in another column | Excel Worksheet Functions | |||
| Multiplying cells with formula's | New Users to Excel | |||
| Formulas for multiplying cells | Excel Worksheet Functions | |||
| Excel - Autom. Filter "Empty / Non Empty cells" should come first | Excel Discussion (Misc queries) | |||
| How can I convert empty strings to empty cells? | Excel Discussion (Misc queries) | |||
Linear Mode
