Nesting more than 7 IF functions

[patryan22]

I have a list with over 20,000 rows in it. Each cell in column D has a
number between 1 and 200,000. I would like to set up a formula in
column E that will show a letter based on what range the number is in.
For example, if the number is between 1 and 500, show "A", if it is
between 501 and 1,000, show "B". If there were only 7 number ranges I
would use a nested if:

=IF(AND(D1>1,D1<500),"A",(IF(AND(D1>500,D1<1000)),"B",(IF.......

and so on...

The problem is that I have 13 number ranges that I need to create codes
for. Any tips?

 

[Mikeopolo]

For example, with:

A1 to A3 : 1, 501,1001
B1 to B3: A, B, C

Name the range A13 "letters" (makes the formula below easier t
understand)

In C1: Any number from 1 up

In D1: =VLOOKUP(C1,letters,2,TRUE)

If I enter a number greater than 1001, I'll get C.

Hope this helps
Regards
mik

 

[vandenberg p]

Hello:

Your best bet is probably the Vlookup function.

In say H1 to I13 create a table that sets the limits of each range.
For example in H1 put 0 and in I1 put A, then in H2 put 500 and in
I2 put B etc. for 13 rows. (In your example what do you want to
happen if the value is exactly 500?) In this example if the value
is 500 it will return a B, if the value is less than 500 it will
return A. So you will need to decide on the break points.

Then in e1 put =VLOOKUP(D1,$H$1:$I$13,2), this assume your
first value is in D1. You can copy this formula down the
entire 20,000 rows.

The other option is to use the following form of the "choose" function:
(formula is entered all on one line)
=CHOOSE((D1<=500)+(D1<=1000)+(D1<=2000)+(D1<=3000)+(D1<=8000)
+(D1<=10000),"f","e","d","c","b","a")

For this example I used just 6 value you would continue on for 7 more. Be sure that
you have the letters in reverse order. For this example if the value in D1 was 9000
the result of the choose calculation would be 1 and would return "f", if the value in
D1 was 450 the choose calculation would be 6 and would return "a."

Pieter Vandenberg

(e-mail address removed) wrote:
: I have a list with over 20,000 rows in it. Each cell in column D has a
: number between 1 and 200,000. I would like to set up a formula in
: column E that will show a letter based on what range the number is in.
: For example, if the number is between 1 and 500, show "A", if it is
: between 501 and 1,000, show "B". If there were only 7 number ranges I
: would use a nested if:

: =IF(AND(D1>1,D1<500),"A",(IF(AND(D1>500,D1<1000)),"B",(IF.......

: and so on...

: The problem is that I have 13 number ranges that I need to create codes
: for. Any tips?

 

[Arvi Laanemets]

Hi

How about a case where you run out of letters (you have 400 groups there)?
The formula below returns groups as A, B, C, ... , Z, AA, AB, ...

=IF(INT(D1/500)<26,CHAR(INT(D1/500)+65),CHAR(INT(INT(D1/500)/26)+64) &
CHAR(MOD(INT(D1/500),26)+65))

 

[Ron Rosenfeld]

I have a list with over 20,000 rows in it. Each cell in column D has a
number between 1 and 200,000. I would like to set up a formula in
column E that will show a letter based on what range the number is in.
For example, if the number is between 1 and 500, show "A", if it is
between 501 and 1,000, show "B". If there were only 7 number ranges I
would use a nested if:

=IF(AND(D1>1,D1<500),"A",(IF(AND(D1>500,D1<1000)),"B",(IF.......

and so on...

The problem is that I have 13 number ranges that I need to create codes
for. Any tips?

Use VLOOKUP.
--ron

 

[aaron.kempf]

not possible.

use a real tool.

like a database for example.

Joins are more powerful than vlookups; any day of the week.

 

[Jeff Standen]

I know I shouldn't do this, but why do you do this? I'm honestly interested.

Jeff

 

[aaron.kempf]

Because I've hit this 7-nested if then limit before also; and it's a
friggin PITA.

if A3 > 0, if A4> 0, etc

it just drvies me crazy I mean
what a piece of shit format.

Excel just isn't as powerful as Access; it can't do the same math; it
doesn't have ANY sort of control.

Excel is a waste of energy for:

a) storing data
b) printing data
c) complex logic
d) entering data

and you guys are stuck in your sandbox-- with only one tool; and you
keep on hitting each other on the head.

I'm like... 'GET OUT OF THE SANDBOX, STOP SWINGING A HAMMER AROUND; AND
LEARN TO WEAR A HARDHAT'

because I want you guys to be successful.

I am probably the most altruistic person you've never met

I really honestly deeply care for each and every one of you.

I used to be stuck in a sandbox with only a hammer.
I kept on telling my boss that I wanted 50% raise after 50% raise...

and he sat me down and said 'you can either be a programmer or a sales
person.. but I'm never going to pay an Excel Dork; Tech Support person
more than X dollars'

I am really really glad that he gave me that advice; i've been a
programmer ever since.

It's all about being able to automate. Excel just doesn't have 1/3rd
of the tools that it needs; because it's trying to solve every business
problem in the world.

They can't add more functionality to excel because it's already a big
fat bloated piece of shit ass software.

But being able to email a PDF?

Access has had this functionality for years; it is called SNP format.
Microsoft Office Snapshot Viewer.

Being able to email a page and then sync the emailed page with the rest
of your data?

That would require a handful of spreadsheets; and a logistics
nightmare.

Excel is just crap. Google is going to kick Microsofts' ASS. Because
Excel hasn't had any innovation for 10 years.

If Microsoft made a web-based Office Web Components application... that
stored simple XML defs in a centralized place; and allowed us to SHARE
DATA instead of emailing spreadsheets?

It would be priceless.

I have a better Excel than Excel; i have better pivotTables than Excel.

I don't have a 65536 limit in Excel i have a 250k row limit.

I don't have performance problems with pivotTables; because i use a
database to store data.

I just think that Excel is the biggest time-waster to ever come out.

More so than MineSweeper and Solitaire, Internet Explorer and Instant
Messaging all rolled into one.

if you have a dozen analysts; each build a dozen spreadsheets.. what
are you stuck with?

A bunch of data that you can't leverage; you can't scale, and you can't
share.

If you have 2 db analysts; each build 2 databases; you are blessed with
a wonderful, scalable solution.

SQL Server is FREE these days.

MSDE or SQL Server Express-- these are FREEE solutions.

type all your data into SQL and then when it's running too slow? Add a
2nd processor instead of rewriting it.

it's just a ton simpler to work with databases than to use Excel;
incorrectly-- by typing data in excel and emailing data in Excel.

If you use a database; you can consume your data in a webpage.

if you use Excel?

you're a ****ing idiot.


-Aaron