Excel / OpenOffice Calc Formula Help

here is updated code we have tried;

http://pastebin.com/m59c3f4ca

better explaination of what we are trying to do;

http://pastebin.com/m5d92de4a

ask me if you need more details.
money-left-on-table.xls

update: http://pastebin.com/m5aa6f895

Hey,
I am not sure I follow what are you looking for exactly here? Can you please explain briefly?

- Ardhendu

did you read the pastebin's?

ok so basically we are trying to do this:

=((D9*40)*.2)-E9*

one of the three following formulas;

IF(F9>=40),*.2
IF(AND(F9>=33,F9<=39.99),*.15
IF(F9<=32.99),*10

how would we combine those three together so that E9 would be multiplied by one of those three formulas.

If I understand correctly, the formula in your pastebin link might be:
=D9*40*.2-E9*VLOOKUP(F9,{0,0.10;33,0.15;40,0.2},2)

This formula tests cell F9. If it is 0 to 32.99, VLOOKUP returns 0.10
If F9 is 33 to 39.99, VLOOKUP returns 0.15
If F9 is 40 or more, VLOOKUP returns 0.2

Brad

ok so I am a noob when it comes to excel, how do I make this all work. thanks

Hi,
Try this formula in H9

=((D9*40)*0.2)-E9*IF(F9>=40,0.2,IF(F9<=32.99,10,0.15))

See if this works...

Ardhendu.
money-left-on-table.xls

ok so it looks like it worked for Geoff but not Logan or AJ, any idea why? looks like it's calculating the 15% but not the 10% or the 20% variable.

-b3ck

awesome it works perfect, is there a way to make it so the dollar amounts don't go negative, so they'll stop at $0 or show a positive amount?

-b3ck

You can enclose the whole formula within an If Loop...

= If(Formula >=0,Formula," ")
where formula is the existing formula on the cell.

- Ardhendu

like I said we are noobs, can you show us an example, thanks.

Lets say your formula is =((D9*40)*0.2)-E9*IF(F9>=40,0.2,IF(F9<=32.99,10,0.15)) then you need to enclose this in an IF condition like this...

= If(Formula >=0,Formula," ")

In this case your formula will become ...

=IF(((D9*40)*0.2)-E9*IF(F9>=40,0.2,IF(F9<=32.99,10,0.15)) >=0,((D9*40)*0.2)-E9*IF(F9>=40,0.2,IF(F9<=32.99,10,0.15))," ")

This might look complicated but if you try this a couple of times its easy to get the hang of this.

Good Luck!
Ardhendu.

I suggest using the MAX function instead of repeating a lengthy calculation. You could use either of the following formulas in cell H9 to return 0 instead of a negative number. They will return the desired value if it happened to be positive.
=MAX(0,((D9*40)*0.2)-E9*IF(F9>=40,0.2,IF(F9<=32.99,10,0.15)),0)
=MAX(0,D9*40*.2-E9*VLOOKUP(F9,{0,0.10;33,0.15;40,0.2},2))

I've attached a copy of Ardhendu's sample file showing both MAX formulas (the IF function on top and the VLOOKUP on the next row).

Brad
money-left-on-tableQ24069066.xls

Thanks a lot Brad... This is even better now.

- Ardhendu.

Nice Badge... BTW...

Wow! thanks guys!