Google sheets formula that needs a maximum variable

[N0t Y0urs]

Hi all,

I can’t figure out what I need to change to add a variable to my formula. Currently it works perfectly but I want it to return a maximum value of 250 once it reaches 250.

This is my formula as is

=IFS($AK$4="EUR/AUD",((($AL45*$AL$4))/$AM$2)*$AO$2,$AK$4="GBP/AUD",((($AL45*$AL$4))/$AM$2)*$AO$2,$AK$4="AUD/CAD",((($AL45*$AL$4))/$AM$2)*$AO$3,$AK$4="EUR/CAD",((($AL45*$AL$4))/$AM$2)*$AO$3,$AK$4="GBP/CAD",((($AL45*$AL$4))/$AM$2)*$AO$3,$AK$4="NZD/CAD",((($AL45*$AL$4))/$AM$2)*$AO$3,$AK$4="USD/CAD",((($AL45*$AL$4))/$AM$2)*$AO$3,$AK$4="AUD/CHF",((($AL45*$AL$4))/$AM$2)*$AO$4,$AK$4="CAD/CHF",((($AL45*$AL$4))/$AM$2)*$AO$4,$AK$4="EUR/CHF",((($AL45*$AL$4))/$AM$2)*$AO$4,$AK$4="GBP/CHF",((($AL45*$AL$4))/$AM$2)*$AO$4,$AK$4="NZD/CHF",((($AL45*$AL$4))/$AM$2)*$AO$4,$AK$4="USD/CHF",((($AL45*$AL$4))/$AM$2)*$AO$4,$AK$4="EUR/GBP",((($AL45*$AL$4))/$AM$2)*$AQ$2,$AK$4="AUD/JPY",((($AL45*$AL$4))/$AM$2)*$AQ$3,$AK$4="CAD/JPY",((($AL45*$AL$4))/$AM$2)*$AQ$3,$AK$4="CHF/JPY",((($AL45*$AL$4))/$AM$2)*$AQ$3,$AK$4="EUR/JPY",((($AL45*$AL$4))/$AM$2)*$AQ$3,$AK$4="GBP/JPY",((($AL45*$AL$4))/$AM$2)*$AQ$3,$AK$4="NZD/JPY",((($AL45*$AL$4))/$AM$2)*$AQ$3,$AK$4="USD/JPY",((($AL45*$AL$4))/$AM$2)*$AQ$3,$AK$4="AUD/NZD",((($AL45*$AL$4))/$AM$2)*$AQ$4,$AK$4="EUR/NZD",((($AL45*$AL$4))/$AM$2)*$AQ$4,$AK$4="GBP/NZD",((($AL45*$AL$4))/$AM$2)*$AQ$4,$AK$4="AUD/USD",((($AL45*$AL$4))/$AM$2)*0.1,$AK$4="EUR/USD",((($AL45*$AL$4))/$AM$2)*0.1,$AK$4="GBP/USD",((($AL45*$AL$4))/$AM$2)*0.1,$AK$4="NZD/USD",((($AL45*$AL$4))/$AM$2)*0.1,$AK$4="XAG/USD",((($AL45*$AL$4))/$AM$2)*0.1,$AK$4="XAU/USD",((($AL45*$AL$4))/$AM$2)*0.1,$AK$4="NAS100",((($AL45*$AL$4))/$AM$2)/#REF!,$AK$4="US30",((($AL45*$AL$4))/$AM$2)/#REF!,$AK$4="S&P500",((($AL45*$AL$4))/$AM$2)/#REF!)

Sorry I am on the move today so only working off my phone so screenshot the results. As I said once it reaches 250 (row 45) I want the remaining results to be 250.

I’ve tried adding a max value in but keep getting errors and went the other way with a min roundup value.

Any assistance would be appreciated. Thanks

 

[RoryA]

If I understand correctly, you can simply wrap that in a MIN(250,your formula) function.

It also appears that your formula multiplier generally depends on the last currency mentioned, so you could simplify that formula quite a lot.

 

[N0t Y0urs]

Thank you that worked. As for simplifying I don’t see how considering I might have 5 different currencies today, tomorrow might have 2 the same plus 4 new one.

But if you think there’s a smarter cleaner way I’m all ears

 

[RoryA]

I'd use a lookup table.