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

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

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)



"Shannon" wrote in message
...
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

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)*D28:D31))

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

XL2002, WinXP


"Shannon" wrote:

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

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:B 31)*(K6<=C28:C31)*D28:D31))

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


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

XL2002, WinXP


"Ron Coderre" wrote:

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)*D28:D31))

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

XL2002, WinXP


"Shannon" wrote:

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

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!

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))

--
Regards

Roger Govier


"Shannon" wrote in message
...
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!

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


"Shannon" wrote:

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!

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" wrote:

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


"Shannon" wrote:

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!

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" wrote:

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))

--
Regards

Roger Govier


"Shannon" wrote in message
...
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!

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

--
Regards

Roger Govier


"Ron Coderre" wrote in message
...
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" wrote:

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))

--
Regards

Roger Govier


"Shannon" wrote in message
...
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!