Quick excel/sheets help please

[audi321]

Hi all, I cannot make a formula work for some reason. What I'm trying to do is have a cell populate with one of 3 "texts" depending on the value of another.

For example, if A1 contains a number between 1 and 500, then B1 should say "Small". If A1 contains a number between 501 and 1000, B1 should say "Medium". If A1 contains a number between 1001 and 1500, then B1 should say "Large".

I'm using Sheets, but I believe excel has the same formulas? From Googling, I believe something with IF and OR is in order, but I just cannot make it work.

Thanks for any advice guys.

[Benjarke]

This should do the trick.

=IF(A1>1500,"",IF(A1>1000,"Large",IF(A1>500,"Medium",IF(A1>=1,"Small",""))))

[J8 SVG]

=IF(A1<500,"small",(IF(A1<1000,"medium",(IF(A1<1500,"Large","Too Big")))))

Not particularly elegant but it will work

[RizzoTheRat]

You can simplify a bit if it's only 3 options and the 1 and 1500 aren't limits, take out the small and large and anything left over is medium

=IF(A1<500,"small",IF(A1>1000,"large","medium"))

Otherwise you you need the slightly more complex versions above to handle values less than 1 or greater than 1500

[Doofus]

You only need two IFs

=IF(A1>1000,"Large",IF(A1>500,"Medium","Small"))

[horselesscarriage]

Assuming that cell B10 is the one you want to test;

=IF(B10>1000,"Large",IF(B10<501,"Small","Medium"))

[audi321]

Thank you guys! I knew the power of PH would shine through. Looks like I was getting confused with the OR part of things then!

[Mr Pointy]

This is a good example of where the IFS function is preferable to nested IF statements. This is the Excel formula but you should be able to convert it to the Sheets equivalent (where A1 is the cell with data):

=IFS(A1<501,"small",A1<1001,"medium",A1<1501,"large","true","Too Large")

https://support.google.com/docs/answer/7014145?hl=...
https://support.microsoft.com/en-us/office/ifs-fun...

[jwilco]

Mr Pointy said:

This is a good example of where the IFS function is preferable to nested IF statements. This is the Excel formula but you should be able to convert it to the Sheets equivalent (where A1 is the cell with data):

=IFS(A1<501,"small",A1<1001,"medium",A1<1501,"large","true","Too Large")

https://support.google.com/docs/answer/7014145?hl=...
https://support.microsoft.com/en-us/office/ifs-fun...

I didn't know about the IFS function. Like you say - much clearer than the nested IF. Thanks!

[RizzoTheRat]

Me neither, that's a lot simpler to follow than nested IFs, thanks.

[sideways sid]

To make life easier for the user in future, don't have absolute values in your but use references.

Then when someone asks for example:

"What if "medium" is 600 to 900?

you can get the answer immediately instead of changing numbers in every row/column/worksheet.

Whether you use names, a data table, or just hardcode into three cells is up to you.

[sideways sid]

To make life easier for the user in future, don't have absolute values in your but use references.

Then when someone asks for example:

"What if "medium" is 600 to 900?

you can get the answer immediately instead of changing numbers in every row/column/worksheet.

Whether you use names, a data table, or just hardcode into three cells is up to you.