Display Zero only in Selected Cells using a formula

Maybe this would suffice:
=IF($A50="Public Injury",CityPanmureDepots!$G31,0)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,700 Files:356 Subscribers:53
xdemechanik
---
"Steved" wrote:
> Hello from Steved
>
> Please using the bottom formula I would like the cell to display "0"
>
> What is required please to acheive this
>
> =IF($A50="Public Injury",SUM(CityPanmureDepots!$G31),0)
>
> I Thankyou.
>
>
>

Hello Pete_UK from Steved

Pete_UK I've unchecked Show a Zero in Cells that have Zero Value

I only want to show "0" in cells that I specify.

Thankyou for taking timeout on my issue.



"Pete_UK" wrote:

> Do you mean "0" as in the text value 0 rather than the numeric value 0
> (which it currently returns if the condition is not met)? I'm not sure
> why you have the SUM function when you are not actually adding
> anything. You can try this:
>
> =IF($A50="Public Injury",CityPanmureDepots!$G31,"0")
>
> or post back with some further detail if this is not what you mean.
>
> Hope this helps.
>
> Pete
>
> On Aug 7, 12:12 am, Steved <Ste... DeleteThis @discussions.microsoft.com> wrote:
> > Hello from Steved
> >
> > Please using the bottom formula I would like the cell to display "0"
> >
> > What is required please to acheive this
> >
> > =IF($A50="Public Injury",SUM(CityPanmureDepots!$G31),0)
> >
> > I Thankyou.
>
>

You're welcome.

Pete

On Aug 7, 12:43 am, Steved <Ste....DeleteThis@discussions.microsoft.com> wrote:
> Hello Pete_UK from Steved
>
> Pete_UK I've unchecked Show a Zero in Cells that have Zero Value
>
> I only want to show "0" in cells that I specify.
>
> Thankyou for taking timeout on my issue.
>

Hello from Steved

I've unchecked Show a Zero in Cells that have Zero Value

I would like to display the value 0 instead off a blank cell

What is required please to add to the bottom formula. I thankyou in advance.

=IF($A50="Public Injury",CityPanmureDepots!$G31,0)



"Max" wrote:

> Maybe this would suffice:
> =IF($A50="Public Injury",CityPanmureDepots!$G31,0)
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:16,700 Files:356 Subscribers:53
> xdemechanik
> ---
> "Steved" wrote:
> > Hello from Steved
> >
> > Please using the bottom formula I would like the cell to display "0"
> >
> > What is required please to acheive this
> >
> > =IF($A50="Public Injury",SUM(CityPanmureDepots!$G31),0)
> >
> > I Thankyou.
> >
> >
> >

Steved,

Think you need to carefully re-assess your actual intents

> I've unchecked Show a Zero in Cells that have Zero Value
Ok, but that's a sheet specific setting, not cell specific. That means every
formula cell returning zero or an input cell with a zero in that sheet will
appear blank. But the underlying value remains a numeric zero.

> I would like to display the value 0 instead off a blank cell
If you've set it in the sheet as above, then you can't have this at the same
time. Not without making the actual numeric zero as a text number, which
would then impact any downstream formulas pointing to the earlier
expression's return, causing you even more problems.

Given the above, this is my best guess for you to try:
1. First, switch on ie check the "Zero values" setting in the sheet. Then
use simple conditional formatting instead on all those specific cells in the
sheet that you want to mask numeric zeros, for visual neatness purposes. Eg
Cell value is: equal to: 0, with font color formatted to be same as the fill
color of the cell.

2. Then for your expression, use this:
=IF($A50="Public
Injury",IF(CityPanmureDepots!$G31="","",CityPanmureDepots!$G31),0)
And don't apply any CF masking on this formula cell.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:16,700 Files:356 Subscribers:53
xdemechanik
---
"Steved" wrote:
> Hello from Steved
>
> I've unchecked Show a Zero in Cells that have Zero Value
>
> I would like to display the value 0 instead off a blank cell
>
> What is required please to add to the bottom formula. I thankyou in advance.
>
> =IF($A50="Public Injury",CityPanmureDepots!$G31,0)

Hello Max

Thankyou.

especially for the explanation and your time.

Steved




"Max" wrote:

> Steved,
>
> Think you need to carefully re-assess your actual intents
>
> > I've unchecked Show a Zero in Cells that have Zero Value
> Ok, but that's a sheet specific setting, not cell specific. That means every
> formula cell returning zero or an input cell with a zero in that sheet will
> appear blank. But the underlying value remains a numeric zero.
>
> > I would like to display the value 0 instead off a blank cell
> If you've set it in the sheet as above, then you can't have this at the same
> time. Not without making the actual numeric zero as a text number, which
> would then impact any downstream formulas pointing to the earlier
> expression's return, causing you even more problems.
>
> Given the above, this is my best guess for you to try:
> 1. First, switch on ie check the "Zero values" setting in the sheet. Then
> use simple conditional formatting instead on all those specific cells in the
> sheet that you want to mask numeric zeros, for visual neatness purposes. Eg
> Cell value is: equal to: 0, with font color formatted to be same as the fill
> color of the cell.
>
> 2. Then for your expression, use this:
> =IF($A50="Public
> Injury",IF(CityPanmureDepots!$G31="","",CityPanmureDepots!$G31),0)
> And don't apply any CF masking on this formula cell.
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> Downloads:16,700 Files:356 Subscribers:53
> xdemechanik
> ---
> "Steved" wrote:
> > Hello from Steved
> >
> > I've unchecked Show a Zero in Cells that have Zero Value
> >
> > I would like to display the value 0 instead off a blank cell
> >
> > What is required please to add to the bottom formula. I thankyou in advance.
> >
> > =IF($A50="Public Injury",CityPanmureDepots!$G31,0)
>