Apple Numbers — IF you expression help

Question

Apple Numbers — IF you expression help

I have a spreadsheet that generates a number (in this case a file size in GBs).

I’m looking for a formula that takes this result and then using a list of memory stick sizes (16, 32, 64, 128 etc) assigns the right size to the result.

This is to find the correct price for the memory stick required to then add to a list of other fees.

For example:

If the resulting first number is 52 GB, that number is compared with 16. if it’s less than 16 then show “£5.00” .

Then this is where I get stuck.

I need to have 52 (or whatever the result is) then compared with the other numbers in the range:

If 52 is more than 16 but less than 32, then show £11.

If 52 is more than 16, and 32 BUT less than 64, then show £15,

If 52 is more than 16, 32, and 64 BUT less than 128, then show £21

. . . and so on.

I’m still not sure even this is right in terms of logic.

Hopefully someone can see what I need to achieve and can come up with either a solution, or a confirmation it can’t be done.

Many thanks in anticipation.

iMac 27″, macOS 11.6

Posted on Feb 27, 2022 7:17 PM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

37,910 points

Feb 27, 2022 9:50 PM in response to CheesedOffWithIT

You can try something like this:

Make a "lookup table" with the top end of the size ranges in column A and the corresponding prices in column B.

Then use a formula like this:

=XLOOKUP(A2,Lookup::A,Lookup::B,"out of range",1)

Or, if your region uses , as a decimal separator, use this:

=XLOOKUP(A2;Lookup::A,Lookup#B;"out of range";1)

You can make your spreadsheet more compact and eliminate the extra lookup table by doing something like this:

=XLOOKUP(A2,{16;32;64;128},{5;11;15;21},"out of range",1)

Or, if your region uses , as a decimal separator:

=XLOOKUP(A2;{16;32;64;128};{5;11;15;21};"out of range";1)

If you want to format the price as Currency simply select that cell and go to Data Format in the panel at the right.

SG

Reply

Answer 2

Feb 28, 2022 3:13 AM in response to SGIII

Thanks again SG — have now tried this and it works beautifully. I've stuck with the look-up table as it's a more useful visual reference for if pricing changes etc.

I'm very much obliged — another formula expression under my belt!

James

Reply

Answer 3

Feb 27, 2022 11:01 PM in response to SGIII

Thanks SG — that sounds promising. I’ll have a look and let you know how it goes. .

Really grateful for your swift response.

James

Reply

Apple Numbers — IF you expression help

Similar questions