Big "IF" Statement?

  • Thread starter Thread starter PJD
  • Start date Start date
P

PJD

I'm trying to score an account by two metrics: GM% on the Y
axis and number of units on the Y. In the past, my scoring
matrix had just four options A, B, C or D. I am now trying
to create sub-quadrants such that an account can be in one
of sixteen (please see below). This is the formula I
successfully use for the four quadrants:

IF(A1>20,IF(U5>50,"A","C"),IF(b1>50,"B","D"))))

I am stumped on how to score 16.... HELP!

TIA



100% A1 | A2 | B1 | B2
| | |
75% ---------------------------------------
A3 | A4 | B3 | B4
50% _______________________________________

C1 | C2 | D1 | D2
25% ---------------------------------------
| | |
0% C3 | C4 | D3 D4
________________________________________________
10 20 30
 
I think we need the question re-phrased. You know what you mean by quadrants
but it must be a very specific usage. Tell us in more simple words (some of
us are none too swift) what is needed and we will have a go at answering.
Bernard
 
PJD said:
I'm trying to score an account by two metrics: GM% on the Y
axis and number of units on the Y. In the past, my scoring
matrix had just four options A, B, C or D. I am now trying
to create sub-quadrants such that an account can be in one
of sixteen (please see below). This is the formula I
successfully use for the four quadrants:

IF(A1>20,IF(U5>50,"A","C"),IF(b1>50,"B","D"))))

I am stumped on how to score 16.... HELP!

TIA



100% A1 | A2 | B1 | B2
| | |
75% ---------------------------------------
A3 | A4 | B3 | B4
50% _______________________________________

C1 | C2 | D1 | D2
25% ---------------------------------------
| | |
0% C3 | C4 | D3 D4
________________________________________________
10 20 30
Click to expand...

Here's one way. You'll need a couple of helper cells and a lookup table.

Helper cell one says...
=IF(GM%<25,25,IF(GM%<50,50,IF(GM%<75,75,IF(GM%<100,100,=NA()))))

Replace GM% with your appropriate cell address (i.e. A1, B1, or U5 from your
example).

Helper cell two says the same thing, except that it compares your number of
units to the proper scales (i.e. <10, <20, <30, or else...)

Your lookup table looks like this:

<blank> 10 20 30 40
25 C3 C4 D3 D4
50 C1 C2 D1 D2
75 A3 A4 B3 B4
100 A1 A2 B1 B2

Now you need to do a double lookup as described on Chip Pearson's page:
http://www.cpearson.com/excel/lookups.htm#DoubleLookup. If your helper
cells return 10 and 50, for example, then the double lookup will return the
value C1.

I hope this is enough to get you going.

Dave
dvt at psu dot edu
 
Back
Top