I am a Novice to Numbers and really spread sheets in general. I am trying to get my spreadsheet to give me the smallest temperature reading for a weeks worth of readings. The cells I am querying are pulled from other sheets and represent as a "0" if there is no data. I want to be able to ignore empty or "0" cells and still get the smallest temperature. Can anyone out there help me out?
Gidian,
You could change the expression in the cell that pulls in the data to check to see if there is anything there first.
For instance, if you are looking at Sheet 1::Table 1::A1 for a value, you could write:
=IF(LEN(Sheet 1::Table 1::A1)>0, Sheet 1::Table 1::A1, "")
Jerry
you can also try something like this:
Average of cells B1 thru H1 (not including zeros):
Average including, but ignoring, zeros:
A2=AVERAGE(B1:L1)×COUNTA(B1:L1)÷(COUNTA(B1:L1)−COUNTIF(B1:L1,0))
I tried something very much like this and it did not work. For some reason (almost certainly user error) when I set the "0" values to represent as nothing it still counts them as zeros for the purpose of getting the smallest number anyway. So the output I get is "blank" instead of "0", but it still does not show me the right number.
If your temperatures are in column B and are all greater than 0 then you can use:
=SMALL(B,COUNTIF(B,0)+1).
It gets more complicated if you have both positive and negative temperatures.
(Row 11 is a Footer Row.)
SG
Hi Gidian,
Jerry's solution works for me. What are you doing that is different?
Formula in A2 of Table 2 (and Fill Down):
=IF(LEN(Table 1::A2)>0, Table 1::A2, "")
If you move a table to a different sheet, the formulas will automatically adjust to include the sheet name, as in Jerry's example.
Formula in A8 of Table 2 (a Footer Row)
=MIN(A)
Rows 1 in both tables are Header Rows.
Regards,
Ian.
Well I didn't have the LEN in there, but basically I did the rest the same. Set it up so that it turned all the "0" values to "" ( I am assuming this mean it changes the value to nothing) but the output of still recognised those entries as either 0 or some kind of null and I still don't get the answer.
=MIN(Low Temps::B2:B8,IF(Low Temps::B2:B8=0,"100",Low Temps::B2:B8)) Is the equation I used. There were a few other tricks I tried, but none of them worked and I don't remember what they were. I don't if it matters but they cells that I am querying are equations themselves and the answers they spit out are "0" I apologize if I am being stupid about this, like I said I am pretty new to all this. Thank you all for the help and patients!
Hey SGIII I just tried this formula and it worked like a charm. Thank you very much! Now can you explain to me why it works?
From Formula & Functions Help (in your Help menu):
The SMALL function returns the nth-smallest value within a set of numeric values. The smallest value is ranked number 1.
SMALL(value-set, ranking)
Notes
A ranking of 1 retrieves the smallest number in the collection, 2 the second-smallest, and so on. Values included in the collection that are of the same size are ranked together, but impact the outcome.
The ranking here is provided by COUNTIF(B,0)+1. This counts the number of zeros in the value-set and adds 1, the result being the ranking of the smallest number that is not a 0).
SG
Brilliant! Thanks!
How do I get min function in Numbers to ignore 0's
