Nested IF

[#1]

Hi All,

Does this if formula look weird to anyone? I'm using it as a expression on the chart although it doesn't show an error it's not filtering out by selections.

if the average of the 6 added columns is equal or less than 500 than Small

if its between 501 to 12000 than Medium     

Else

Large.

=if(([2015 09] + [2015 10] + [2015 11] + [2015 12] + [2016 01] + [2016 02]/ 6) <= 500, 'Small', if(([2015 09] + [2015 10] + [2015 11] + [2015 12] + [2016 01] + [2016 02]/ 6) <= 12000,'Medium', 'Large'))

Thanks in Advance,

[sunny_talwar · Accepted solution]

Can you try this:

=If(RangeAvg([2015 09], [2015 10], [2015 11], [2015 12], [2016 01], [2016 02]) <= 500, 'Small',

  If(RangeAvg([2015 09], [2015 10], [2015 11], [2015 12], [2016 01], [2016 02]) <= 12000, 'Medium', 'Large'))

or

=If(RangeSum([2015 09], [2015 10], [2015 11], [2015 12], [2016 01], [2016 02])/6 <= 500, 'Small',

  If(RangeSum([2015 09], [2015 10], [2015 11], [2015 12], [2016 01], [2016 02])/6 <= 12000, 'Medium', 'Large'))

[agustinbobba]

Hi!,

Try this..

if(sum([2015 09] + [2015 10] + [2015 11] + [2015 12] + [2016 01] + [2016 02])/ 6) <= 500, 'Small',
     if(sum([2015 09] + [2015 10] + [2015 11] + [2015 12] + [2016 01] + [2016 02])/ 6) <= 12000,'Medium', 'Large'))

best regards,

Agustin

[#4]

Thanks Sunny. For the Average column in the table it shows zero even though there are values in the preceding months. Please provide assistance.

[#5]

Can someone please provide assistance!!!!!!!!!!!

There are two values which belong to Small category but once I click on Middle they show up. Please help!!!!!

[Peter_Cammaert]

Your example doesn't match your OP.

In a chart expression entry field, you can either refer to actual field names if you embed them in an aggregation function call ( like e.g. Sum(OctRound) where OctRound is a field name), or to the label of another expression column, on condition that you actually do label them.

If you label each expression column with for instance a dummy text value (eg Col1, Col2, Col3) you can use either Sunny's RangeAvg trick (e.g. RangeAvg(Col1, Col2, Col3, ...) ), a tweak of Augustins explicit averaging trick (e.g. RangeSum(Col1, Col2,...)/6 ) or a very complex expression like (Sum(SepRound)+Sum(OctRound)+...)/6 to get your intended results.

You do not really have to label your expression columns. You can also use Column indices and refer to their calculated value by way of the Column() function. However this is lesser reliable technique as columns may change places, but the index numbers (just add an expresion or a dimension in front) in your expression will not.

Best,

Peter