I am trying to total (SUM) the 5 lowest numbers in a row but I'm getting 'The formula contains a number outside the valid range' error. I don't understand what this error means, what am I doing wrong? Thanks Dave
Hi Dave,
The SMALL function takes one rank only, not a collection (list).
From the Function 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)
value-set: A collection of number values, date/time values or duration values. All values must be of the same value type.
ranking: A number value representing the size ranking of the value you want to retrieve. ranking must be in the range of 1 to the number of values in the collection.
Try this:
Regards,
Ian.
Hi Dave,
The SMALL function takes one rank only, not a collection (list).
From the Function 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)
value-set: A collection of number values, date/time values or duration values. All values must be of the same value type.
ranking: A number value representing the size ranking of the value you want to retrieve. ranking must be in the range of 1 to the number of values in the collection.
Try this:
Regards,
Ian.
The problem likely translates because you're familiar with Excel (or are following some Excel-focussed guide).
Excel does support ranges in this way, where {1, 2, 3, 4, 5} is interpreted as parameters to the SMALL() function, but Numbers does not support this format.
The most straightforward solution is longhand: where you extract the nth values and add them:
=SMALL(D35:J35, 1)+ SMALL(D35:J35,2) + SMALL(D35:J35,3)+ SMALL(D35:J35,4)+ SMALL(D35:J35,5)
There was a similar thread recently about other approaches, but I can't find it off-hand.
Thanks for your reply, it was very helpful!
Thanks for your reply, it was very helpful!
The formula contains a number outside the valid range.
