Log In
Register
Home
January 24, 2023
Offline
Hi All,
I have the following formulas that work well but it would be beneficial if they did NOT return the zero (0) results.
=IF($A4="","",COUNTIF($L$9:$L$250,$A4&"*"))
this ones an array
{=SUM(IF(ISNUMBER(FIND($A6,C$9:C$250)),VALUE(RIGHT(C$9:C$250,FIND($A6,C$9:C$250)+1)),0))}
Just trying to make things look a bit smarter when I graph all the data
TIA
Paul
Trusted Members
October 17, 2018
Offline
The result will have to show something, a blank or a 0but if you perform calculations on these columns a blank cell might cause issues.
What you can do is add a Conditional format to the column or columns that if the value is 0 to make the font color white, that way it is there but you won't see it.
Another way is the Custom Number format, you format it only por ppositive and negative numbers and nothing for zero's
0;0;
0.0;0.0;
;;; will show an empty cell
Hope my explanation makes sense
Trusted Members
Moderators
November 1, 2018
Offline
Can you clarify the "when I graph all the data" comment? Charts will only generally ignore blank cells (no cell that contains a formula is truly blank) but for some chart types there can be workarounds, such as returning #N/A instead.
January 24, 2023
Offline
NO NEED TO ADVISE - LEFT HERE AS A MATTER OF INTEREST - ISSUE RESOLVED (Just made the default in their SharePoint List default as 0 for the relevant columns)
Hi Gents,
The when I graph is probably a red herring; what I'm really looking at is getting a return of nothing if there is no number.
There are two formulas at play here
The first looks at a specific row on an owssvr; this looks at a specific week and extracts two pieces of data (these being)
1. A Unit, and
2 the number of people in that unit (TTS 5, CRTS 103 etc.)
{=SUM(IF(ISNUMBER(FIND($A6,C$9:C$250)),VALUE(RIGHT(C$9:C$250,FIND($A6,C$9:C$250)+1)),0))}
The fomula above then looks for the unit (A6) in the column (C9: C250) if it finds it, then it retrns the number of pers in that unit for that week VALUE(RIGHT(C$9:C$250,FIND($A6,C$9:C$250)+1)),0))}
The issue is if; the wek period has not yet arrived, or if someone hasn't completed the data for the owssvr thenn there is no number to return so I get a #Value error.
I can live with it; but was just wondering if there was a way to get it to return blank if no number exists
Cheers and as always
TIA
1 Guest(s)
