problems with nested IF

CharJT

New Member
Hi

I'm trying to create a spreadsheet for my husband, who has to pay commissions based on the amount. However, no matter what is typed in D3, the result is always 4.8. (I get that I goofed up somewhere...I just can't figure out where!)

This is the equation:

=IF(AND(D3>0.01,D3<0.99),0.3,IF(AND(D3>1,D3<9.99),0.35,IF(AND(D3>10,D3<24.99),0.6,IF(AND(D3>25,D3<49.99),1.2,IF(AND(D3>50,D3<199.99),2.4,IF(AND(D3>200,D3<499.99),3.6,4.8))))))

What have I done wrong?

Char

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
you missed integers inbetween the ranges

got it...thanks! (must just be too late...)

Try to use a lookup table of commissions and a lookup formula instead of a chain of IFs.

Replies
1
Views
393
Replies
4
Views
408
Replies
5
Views
679
Replies
4
Views
517
Replies
7
Views
542

1,219,914
Messages
6,150,933
Members
450,994
Latest member
MacOrch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back