If formula not working, 5 arguments and only allowed 3

Question

Level 1

7 points

If formula not working, 5 arguments and only allowed 3

Hi there,

I already have a formula that works in a cell saying, cell F is G divided by 2.5, but if that value is less than .5, then cell F equals .2.

Now I am wanting to add another argument: if cell F is larger than 1.6, it needs to equal 1.5.

What I am trying to say, is every garment has a tolerance allowed for the final garment which is generally divided but 2.5. However, if a pant has a 10cm grade, 4cm is NOT within tolerance. If a pant shrinks 4cm in length, it will not fit.

I tried to work out the formula myself....but just made a mess of it.....as per unusual.

Many thanks to the amazing community team.

Hayley

MacBook Pro (M1, 2020)

Posted on May 27, 2023 10:35 PM

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

30,456 points

May 29, 2023 11:58 AM in response to HayleyHatzi

Simple change:

=IFS(G2/2.5>1.6,1.5,G2/2.5<0.5,0.2,true,G2/2.5)

The formula you requested has two discontinuities in it. It abruptly increases from 0.2 to 0.5 and it goes above 1.5 then drops back down.

Reply

Answer 2

Barry

Level 9

60,023 points

May 28, 2023 1:30 AM in response to HayleyHatzi

Hi Hayley,

IF works with a single requirement. For a set of several 'IFS' to be tested in sequence, use IFS

The IFS function tests each requirement in turn, and returns the 'THEN' calculation for the first IF whose requirement is met.

Here are the results of several vlues in column F, The IFS formula shown below the table is entered in G2 and filled down to show the result for each of the vlaues in column F.

Regards,

Barry

Reply

Answer 3

HayleyHatzi Author

Level 1

7 points

May 28, 2023 2:58 PM in response to Barry

HI Barry,

Excellent....just one problem. The first and most important part of the formula is to divide to orange cell by 2.5. Only then, for example if the the orange cell was 0 or less than .5, the answer will be .2 (as there always needs to be some tolerance, even on 0). Then, if the orange cell divided by 2.5 is larger than 1.6, the answer is to be 1.5 (as anything outside of this is outside of tolerance).

If you compare the two screen shots, one is before the new formula and one is after, the top amounts are incorrect at 1.5.

Can this work in a formula?

Many thanks,

Hayley

Reply

Answer 4

HayleyHatzi Author

Level 1

7 points

May 29, 2023 2:40 PM in response to Badunit

AMAZING! Thank you!

Reply