|
To leave the cell Blank
Hello from Steved
If the cell has an 0 as a value what is required please to make it blank. =SUMPRODUCT(('Planned Kilometres'!$A$4:$A$109=1)*('Planned Kilometres'!$B$4:$B$109="P")*'Planned Kilometres'!$AC$4:$AF$109). Thankyou. |
Hi
3 choices: 1: Wrap the formula inside an IF function: =IF(formula=0,"",formula) That will make the formula twice as long. 2: Goto ToolsOptionsView Uncheck: Zero values 3: Use conditional formatting Select the cell Goto FormatConditional Formatting Cell value is equal to 0 Set the font color to be the same as the fill color. Biff "Steved" wrote in message ... Hello from Steved If the cell has an 0 as a value what is required please to make it blank. =SUMPRODUCT(('Planned Kilometres'!$A$4:$A$109=1)*('Planned Kilometres'!$B$4:$B$109="P")*'Planned Kilometres'!$AC$4:$AF$109). Thankyou. |
I usually do it with an IF test like this...
=IF(SUMPRODUCT(('Planned Kilometres'!$A$4:$A$109=1)*('Planned Kilometres'!$B$4:$B$109="P")*'Planned Kilometres'!$AC$4:$AF$109)=0,"",SUMPRODUCT(('Plann ed Kilometres'!$A$4:$A$109=1)*('Planned Kilometres'!$B$4:$B$109="P")*'Planned Kilometres'!$AC$4:$AF$109)) Roy "Steved" wrote: Hello from Steved If the cell has an 0 as a value what is required please to make it blank. =SUMPRODUCT(('Planned Kilometres'!$A$4:$A$109=1)*('Planned Kilometres'!$B$4:$B$109="P")*'Planned Kilometres'!$AC$4:$AF$109). Thankyou. |
I thankyou Both.
"Roy" wrote: I usually do it with an IF test like this... =IF(SUMPRODUCT(('Planned Kilometres'!$A$4:$A$109=1)*('Planned Kilometres'!$B$4:$B$109="P")*'Planned Kilometres'!$AC$4:$AF$109)=0,"",SUMPRODUCT(('Plann ed Kilometres'!$A$4:$A$109=1)*('Planned Kilometres'!$B$4:$B$109="P")*'Planned Kilometres'!$AC$4:$AF$109)) Roy "Steved" wrote: Hello from Steved If the cell has an 0 as a value what is required please to make it blank. =SUMPRODUCT(('Planned Kilometres'!$A$4:$A$109=1)*('Planned Kilometres'!$B$4:$B$109="P")*'Planned Kilometres'!$AC$4:$AF$109). Thankyou. |
Someday people will learn that long sheet names really do suck! <vbg
Biff "Steved" wrote in message ... I thankyou Both. "Roy" wrote: I usually do it with an IF test like this... =IF(SUMPRODUCT(('Planned Kilometres'!$A$4:$A$109=1)*('Planned Kilometres'!$B$4:$B$109="P")*'Planned Kilometres'!$AC$4:$AF$109)=0,"",SUMPRODUCT(('Plann ed Kilometres'!$A$4:$A$109=1)*('Planned Kilometres'!$B$4:$B$109="P")*'Planned Kilometres'!$AC$4:$AF$109)) Roy "Steved" wrote: Hello from Steved If the cell has an 0 as a value what is required please to make it blank. =SUMPRODUCT(('Planned Kilometres'!$A$4:$A$109=1)*('Planned Kilometres'!$B$4:$B$109="P")*'Planned Kilometres'!$AC$4:$AF$109). Thankyou. |
To leave the cell Blank
Hi Biff,
I'm working on a similar situation that Steved asked you. The problem that I have is that if I use =IF(formula=0,"",formula), the cell the left blank after this formula is regarded as text (because of the ""), but I'd like to pick up that value for calculation the next row, and it will show as #VALUE! error. How can I avoid this? I'm working on something similar to a logging balance sheet that I don't want to show the same value for the rest of the sheet if the log doesn't reach a certain line yet. Thank you, Neon "Biff" wrote: Hi 3 choices: 1: Wrap the formula inside an IF function: =IF(formula=0,"",formula) That will make the formula twice as long. 2: Goto ToolsOptionsView Uncheck: Zero values 3: Use conditional formatting Select the cell Goto FormatConditional Formatting Cell value is equal to 0 Set the font color to be the same as the fill color. Biff "Steved" wrote in message ... Hello from Steved If the cell has an 0 as a value what is required please to make it blank. =SUMPRODUCT(('Planned Kilometres'!$A$4:$A$109=1)*('Planned Kilometres'!$B$4:$B$109="P")*'Planned Kilometres'!$AC$4:$AF$109). Thankyou. |
To leave the cell Blank
You can do this:
=IF(formula=0,0,formula) However, it sounds like you would want the cells to be blank but since we don't know exactly what you're doing I can't suggest anything more specific. Post back with more *specific details*. -- Biff Microsoft Excel MVP "Neon520" wrote in message ... Hi Biff, I'm working on a similar situation that Steved asked you. The problem that I have is that if I use =IF(formula=0,"",formula), the cell the left blank after this formula is regarded as text (because of the ""), but I'd like to pick up that value for calculation the next row, and it will show as #VALUE! error. How can I avoid this? I'm working on something similar to a logging balance sheet that I don't want to show the same value for the rest of the sheet if the log doesn't reach a certain line yet. Thank you, Neon "Biff" wrote: Hi 3 choices: 1: Wrap the formula inside an IF function: =IF(formula=0,"",formula) That will make the formula twice as long. 2: Goto ToolsOptionsView Uncheck: Zero values 3: Use conditional formatting Select the cell Goto FormatConditional Formatting Cell value is equal to 0 Set the font color to be the same as the fill color. Biff "Steved" wrote in message ... Hello from Steved If the cell has an 0 as a value what is required please to make it blank. =SUMPRODUCT(('Planned Kilometres'!$A$4:$A$109=1)*('Planned Kilometres'!$B$4:$B$109="P")*'Planned Kilometres'!$AC$4:$AF$109). Thankyou. |
To leave the cell Blank
Okay, what I'm doing is similar to a mileage log. A column named "Balance"
is at the far right of the sheet and that's where the running balance is at. A little bit different from the Mileage log that is always positive entry (adding up to the balance), my logging sheet will deduct of plus the value of the entry to the previous line balance accordingly. What I'd like is to show the Balance only up to the point where the last entry is, not all the way to the end of the sheet. I'd like to leave the rest of the Balance where there are no any entry BLANK, regardless of the balance is positive negative or even zero. Thank you, Neon "T. Valko" wrote: You can do this: =IF(formula=0,0,formula) However, it sounds like you would want the cells to be blank but since we don't know exactly what you're doing I can't suggest anything more specific. Post back with more *specific details*. -- Biff Microsoft Excel MVP "Neon520" wrote in message ... Hi Biff, I'm working on a similar situation that Steved asked you. The problem that I have is that if I use =IF(formula=0,"",formula), the cell the left blank after this formula is regarded as text (because of the ""), but I'd like to pick up that value for calculation the next row, and it will show as #VALUE! error. How can I avoid this? I'm working on something similar to a logging balance sheet that I don't want to show the same value for the rest of the sheet if the log doesn't reach a certain line yet. Thank you, Neon "Biff" wrote: Hi 3 choices: 1: Wrap the formula inside an IF function: =IF(formula=0,"",formula) That will make the formula twice as long. 2: Goto ToolsOptionsView Uncheck: Zero values 3: Use conditional formatting Select the cell Goto FormatConditional Formatting Cell value is equal to 0 Set the font color to be the same as the fill color. Biff "Steved" wrote in message ... Hello from Steved If the cell has an 0 as a value what is required please to make it blank. =SUMPRODUCT(('Planned Kilometres'!$A$4:$A$109=1)*('Planned Kilometres'!$B$4:$B$109="P")*'Planned Kilometres'!$AC$4:$AF$109). Thankyou. |
To leave the cell Blank
On Apr 1, 5:18 pm, Neon520 wrote:
Okay, what I'm doing is similar to a mileage log. A column named "Balance" is at the far right of the sheet and that's where the running balance is at. A little bit different from the Mileage log that is always positive entry (adding up to the balance), my logging sheet will deduct of plus the value of the entry to the previous line balance accordingly. What I'd like is to show the Balance only up to the point where the last entry is, not all the way to the end of the sheet. I'd like to leave the rest of the Balance where there are no any entry BLANK, regardless of the balance is positive negative or even zero. Thank you, Neon "T. Valko" wrote: You can do this: =IF(formula=0,0,formula) However, it sounds like you would want the cells to be blank but since we don't know exactly what you're doing I can't suggest anything more specific. Post back with more *specific details*. -- Biff Microsoft Excel MVP "Neon520" wrote in message ... Hi Biff, I'm working on a similar situation that Steved asked you. The problem that I have is that if I use =IF(formula=0,"",formula), the cell the left blank after this formula is regarded as text (because of the ""), but I'd like to pick up that value for calculation the next row, and it will show as #VALUE! error. How can I avoid this? I'm working on something similar to a logging balance sheet that I don't want to show the same value for the rest of the sheet if the log doesn't reach a certain line yet. Thank you, Neon "Biff" wrote: Hi 3 choices: 1: Wrap the formula inside an IF function: =IF(formula=0,"",formula) That will make the formula twice as long. 2: Goto ToolsOptionsView Uncheck: Zero values 3: Use conditional formatting Select the cell Goto FormatConditional Formatting Cell value is equal to 0 Set the font color to be the same as the fill color. Biff "Steved" wrote in message ... Hello from Steved If the cell has an 0 as a value what is required please to make it blank. =SUMPRODUCT(('Planned Kilometres'!$A$4:$A$109=1)*('Planned Kilometres'!$B$4:$B$109="P")*'Planned Kilometres'!$AC$4:$AF$109). Thankyou. Instead of =IF(formula=0,"",formula)...try =IF('dataentrycell'=0,"",formula). Assuming the data entry area is left blank or as a 0. |
| All times are GMT +1. The time now is 11:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com