Formula Help please

[Shannon]

I have a problem I just cant seem to get solved.

If a cell (L6) contains an "x" and if cell (K6) is = to or < than cell (C27)
(multiply cell (I6) by cell (J6)) and those results by cell (D27) = VALUE

HOWEVER, IF cell (K6) is = to or > than cell (B28) but = to or < cell (C28)
(multiply cell (I6) by cell (J6)) and those results by cell (D28) = VALUE

HOWEVER, IF cell (K6) is = to or > than cell (B29) but = to or < cell (C29)
(multiply cell (I6) by cell (J6)) and those results by cell (D29) = VALUE

HOWEVER, IF cell (K6) is = to or > than cell (B30) but = to or < cell (C30)
(multiply cell (I6) by cell (J6)) and those results by cell (D30) = VALUE

HOWEVER, IF cell (K6) is = to or > than cell (B31) but = to or < cell (C31)
(multiply cell (I6) by cell (J6)) and those results by cell (D31) = VALUE

HOWEVER, IF cell (K6) is = to or > than cell (B30) (multiply cell (I6) by
cell (J6)) and those results by cell (D32) = VALUE

 

[Shannon]

Sorry posted before I actually asked my question... this is how frazzled this
formula has me. I can see the logic but I cant figure out how to actually
write this formula out. Any suggestions would be greatly appreciated!

Thanks!
Shannon

 

[Bob Phillips]

My reading of this is that you need different formulae in D27, D28, etc.

Here are the first two

=IF(AND(L6="x",K6<=C27),I6*J6,"")

=IF(AND(L6="x",K6>=B28,K6<=C28),I6*J6,"")

you should be able to work out the rest.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Ron Coderre]

First, you've got an ambiguity in your criteria.....

I understand the first part:
K6 AND
IS K6 THEN
<=C27 (N/A) I6*J6* D27

=B28 <=C28 I6*J6* D28
=B29 <=C29 I6*J6* D29
=B30 <=C30 I6*J6* D30
=B31 <=C31 I6*J6* D31

But the last one

=B30 (N/A) I6*J6* D32

conflicts with this (from above)

=B30 <=C30 I6*J6* D30

So.....ignoring the last criteria,
Try this:
=I6*J6*IF(K6<=C27,1,SUMPRODUCT((K6>=B28:B31)*(K6<=C28:C31)*D2831))

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Ron Coderre]

Ooops! I missed the L6 criteria...but, that could just be prepended to my
posted formula (Assuming L6="x" applies to ALL situations)

Possibly this:
=(L6="x")*I6*J6*IF(K6<=C27,1,SUMPRODUCT((K6>=B28:B31)*(K6<=C28:C31)*D2831))

or...this
=IF(L6="x",I6*J6*IF(K6<=C27,1,SUMPRODUCT((K6>=B28:B31)*(K6<=C28:C31)*D2831)),"na")


Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Shannon]

Probably, I'm not explaining this right... Maybe this will help a little more.
I have an employee (John Smith) who is billable to a client, I earn
commission based on his Gross Margin $.

So next to John's name I have 3 cells (AO, AM and RCR). If I am his AM, so
there is an x (CELL is M6)

GM% = 20% (CELL is K6)

GM$ = $12.68 (CELL is J6)

Monthly Hours = 160 (CELL is I6)

Here are the commissions percentages
Margin % COMMISSION
< or = 19% 4%

20% but < or = 24% 6.00%
25% but < or = 29% 8.00%
30% but < or = 34% 10.00%
35% but < or = 39% 12.00%
/= 40% 14.00%

So, if John works 160 hours in a month and his GM% is >19%, I would earn 4%
of the Gross Margin$ for every hour he works. (($12.68*0.04)*160). I need
the calculations to look at (K6) and if is it >20% but <or= 24% use 6% and so
on and so on....

Make any more sense?

Thanks for all your help on this!

 

[Roger Govier]

Hi Shannon

If you set up a table as follows
0% 4%
20% 6%
25% 8%
30% 10%
35% 12%
100% 14%

Name this table Commissions, by marking the range of cells where you
have entered the table, then typing Commissions in the Name box and
pressing Enter.
(The Name box is the small white pane to the left of column A and above
Row 1)

Then use the formula
=IF(M6<>"x","",J6*I6*VLOOKUP(K6,commissions,2))

 

[Ron Coderre]

Try something like this:

With
I6: (Mthly Hours......eg 160)
J6: (GM Dollars.......eg $12.68)
K6: (GM Pct............eg 20%)
M6: (AM Flag..........eg "X" if Yes)

AND.....this table in A1:B6
0% 4%
20% 6%
25% 8%
30% 10%
35% 12%
40% 14%

Then
This formula returns the amount due to you...or N/A
=IF(O6="X",K6*L6*VLOOKUP(M6,A1:B6,2,1),"N/A")

In the above example, the formula returns: $284.03

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Ron Coderre]

Typo.....I had inserted a column while playing with the formula...

With
I6: (Mthly Hours......eg 160)
J6: (GM Dollars.......eg $12.68)
K6: (GM Pct............eg 20%)
M6: (AM Flag..........eg "X" if Yes)

it should be:
=IF(M6="X",I6*J6*VLOOKUP(K6,A1:B6,2,1),"N/A")

***********
Regards,
Ron

XL2002, WinXP

 

[Ron Coderre]

I think you missed a tier at 40%....

Your formula calcs everything from 35% to 100% at a 12% rate
and >=100% at 14%

instead of....
35% up to 40% at 12%
40% and over at 14%

***********
Regards,
Ron

XL2002, WinXP

 

[Roger Govier]

Hi Ron

You are absolutely correct, of course.
I was busy typing away thinking that I needed to set the top at 100%,
but of course with the top set at 40%, everything above would
automatically fall into that category with the default True for the
Vlookup.

That's what you get when you just type and don't try
I shouldn't be so lazy<bg>