Check out some similar questions!

Helpinlasvegas1 · Sep 16, 2009, 03:17 PM

In excel I have 8 different categories for customer level, but to get to any one level there is three different categories that they must meet. I must have all 8 levels associated in one cell while at the same time evaluating the customers numbers to see if they qualify for any of the 8 levels. This is what I came up with =IF(AND(I6>325000,J6>575000,L6>250000),O6,”13”,IF( AND(I6>250000,J6>450000,L6>200000),O6,”12”,IF(AND( I6>200000,J6>350000,L6>150000),O6,”75000”,IF(AND(I 6>125000,J6>225000,L6>100000),O6,”50000”IF(AND(I6> 75000,J6>150000,L6>45000),O6,”30000”,IF(AND(I6>500 00,J6>100000,L6>30000),O6,”16000”,IF(AND(I6>25000, J6>40000,L6>10000),O6,”8000”,IF(AND(I6>10000,J6>20 000,L6>5000),O6,”3000”))))))))).

O6 is where I want the formula to be evaluated at and populate the correct # category.

Helpinlasvegas1 · Sep 16, 2009, 03:19 PM

In excel I have 8 different categories for customer level, but to get to any one level there is three different categories that they must meet. I must have all 8 levels associated in one cell while at the same time evaluating the customers numbers to see if they qualify for any of the 8 levels. This is what I came up with =IF(AND(I6>325000,J6>575000,L6>250000),O6,”13”,IF( AND(I6>250000,J6>450000,L6>200000),O6,”12”,IF(AND( I6>200000,J6>350000,L6>150000),O6,”75000”,IF(AND(I 6>125000,J6>225000,L6>100000),O6,”50000”IF(AND(I6 > 75000,J6>150000,L6>45000),O6,”30000”,IF(AND(I6>500 00,J6>100000,L6>30000),O6,”16000”,IF(AND(I6>25000, J6>40000,L6>10000),O6,”8000”,IF(AND(I6>10000,J6>20 000,L6>5000),O6,”3000”))))))))).

O6 is where I want the formula to be evaluated at and populate the correct # category.

JBeaucaire · Sep 16, 2009, 06:33 PM

Forget the formula and explain the tiers and the logic. This will most likely be a MUCH shorter formula with only a couple of IFs once you explain the tiers.

You're trying to put one of these values into O6?
13
12
75000
50000
30000
16000
8000
3000

JBeaucaire · Sep 16, 2009, 06:56 PM

REMOVED - wrong, I was oh, so wrong. See below for final solution.

Helpinlasvegas1 · Sep 17, 2009, 08:47 AM

Yes, so one of those values will go into O6, based on if the three categories it is evaluating meets the requirements on all three levels. Thanks for the help I will try it.

JBeaucaire · Sep 17, 2009, 09:56 AM

Woah, Nelly, that didn't work. My apologies, I read the whole thing backwards. Duh. First, for a lookup like this to work, the table has to be sorted ASCENDING, like so:

Code:

	  Q	  R	  S	  T
1	10000	20000	5000	3000
2	25000	40000	10000	8000
3	50000	100000	30000	16000
4	75000	150000	45000	30000
5	125000	225000	100000	50000
6	200000	350000	150000	75000
7	250000	450000	200000	12
8	325000	575000	250000	13

Next, the formula in O6 would have to be this little beast:

=INDEX($T$1:$T$8, MATCH($L6, $S$1 : INDEX($S$1:$S$8, MATCH($J6, $R$1 : INDEX($R$1:$R$8, MATCH($I6, $Q$1:$Q$8, 1), 1), 1))))

Helpinlasvegas1 · Sep 17, 2009, 10:38 AM

So these are the amounts in i,j,k 6 and these numbers go up throughout the year and as they reach the matching corresponding levels i want it to populate the number that is given in t on the table you made, and have that answer go into o6

$400 $9,075 $3,342

Helpinlasvegas1 · Sep 17, 2009, 11:58 AM

It works, your amazing, thank you thank you thank you!!

Helpinlasvegas1 · Sep 17, 2009, 12:10 PM

So I understand everything you did, except for the 1),1)1,) at the end could you tell what this does so that I know for furture reference. Thanks again

JBeaucaire · Sep 17, 2009, 04:41 PM

No, I think you're misreading it. I colored that originally here in the forum in hopes of making it clear which section constitute a logic section.

It's not 1,1,1. Look at the coloring I did above again and you'll see that each one of those 1s is the final parameter in a MATCH() formula.

The final parameter in a MATCH() formula is 1 or 0, 0 means "exact match required", and 1 means "fuzzy match ok...round down in chart to closest value."

The first 1 feeds the "fuzzy" instruction to the search in column Q. The second 1 feeds it to the search in column R, and the third instructs it to the search in column S.

ROLCAM · Sep 19, 2009, 05:01 AM

The formula is incorrect, I entered into
A cell of a spreadsheet and nothing happened.
Go back and see where you are going wrong.

ScottGem · Sep 19, 2009, 05:35 AM

If you want the result to appear in O6 the formula needs to be in O6. So that part of your formula is incorrect.

When building a complex IF statement like this, its best to build it up piecemeal. Build it one clause at a time, then combine them one at a time.

For example you start with (in O6):

=IF(AND(I6>325000,J6>575000,L6>250000,”13”,"False" )

Then create another IF in a separate cell for the False condition. If it works, then substitute that for "False" in O6. Then repeat the process for each condition to be tested.

JBeaucaire · Sep 19, 2009, 03:36 PM

Sorry you didn't notice that this was a duplicate thread to here:
https://www.askmehelpdesk.com/spread...la-397026.html
Final solution is in post #5.

Helpinlasvegas1 · Oct 14, 2009, 09:46 AM

I have an excel spreadsheet formula that works wonderfully, but I would like if the conditions have not been meet for it to read something other than "False". Is this possible?

KISS · Oct 14, 2009, 09:57 AM

You mean somethiig like this:

=IF(2=3,5,"no way")

If 2=3, it returns "5" otherwise it returns "no way"

Helpinlasvegas1 · Oct 14, 2009, 10:05 AM

The formula is already so long that when I tried to enter another if staement so there would be no false returns it was too much for excel

Helpinlasvegas1 · Oct 14, 2009, 10:08 AM

=IF(G6>1250000,"Platinum",IF(G6>1000000,"Gold", IF(G6>750000,"Silver", IF(G6>500000,"*****", IF(G6>300000,"****", IF(G6>200000,"***", IF(G6>100000,"**", IF(G6>50000,"*"))))))))

This is my formula. In the end anything less than 50000 returns false. Excel will not allow me to add onto this formula to return a different " " answer. Is there any other way to change the outcome of "false" when the number is less than 50,000

JBeaucaire · Oct 14, 2009, 10:58 AM

If you're evaluating the same cell over and over with multiple IF statements trying to match a particular value or text string, then IF statements are the wrong way. This is what LOOKUP and VLOOKUP and INDEX/MATCH is all about.

Post up your workbook and point out the issue, or at lease post up your current formula and desired additions. I'm sure we can offer something far more robust than IF/IF/IF/IF...

KISS · Oct 14, 2009, 11:08 AM

Helpinlasvegas. Keep the same topics togeter as threads.

Read "Answer this question" as "Reply to this question". It's the fault of the software makers.

I've asked that your threads be combined.

KISS · Oct 14, 2009, 11:13 AM

=IF(G6>1250000,"Platinum",IF(G6>1000000,"Gold", IF(G6>750000,"Silver", IF(G6>500000,"*****", IF(G6>300000,"****", IF(G6>200000,"***", IF(G6>100000,"**", IF(G6>50000,"*","no way"))))))))