# IF + If-And formula not calculating correctly

1. ## IF + If-And formula not calculating correctly

Dear All,

I have a formula as per below:

Formula:  `Please Login or Register  to view this content.`

=IFERROR(IF(D3=0,0,IF(D3>2500,D3-(D3*\$G\$3%),IF(AND(D3<=2500,D3-(D3*\$H\$3%)<=100),D3-\$F\$3,D3-(D3*\$H\$3%)))),0)

B3 = net amount. This cell has the formula
D3 = Cost
F3 = 100.00
G3 = 10 (10%)
H3 = 25 (25%)

A) if cost is more than 2500 then net amount should be 2500-10%

B) if cost is less than or equal to 2500 then net amount should be 2500-25%

C) if cost is a) less than or equal to 2500 & b) cost - 25% is less than or equal to 100 then net amount should be cost-100

The formula seems to work fine for Point A and B.

For point C the formula seems to work fine. If the cost is 100 then net amount shows as 0.00, if the cost is 50 then net amount shows as -50.00. For cost between 100 to 133 the net amount shows the cost-100, however for some reason this only works fine for cost from 100 to 133 and if the cost is 134 and more, the formula shows the result as per point B

I will be thankful if someone can guide me to correct/amend the formula and explain the reason of this mis-calculation for point C.

With kind regards,  Register To Reply

2. ## Re: IF + If-And formula not calculating correctly

perhaps
=IFERROR(IF(D3=0,0,IF(D3>2500,D3-(D3*\$G\$3%),IF(D3-(D3*\$H\$3%)<=100,-100,D3-(D3*\$H\$3%)))),0)

you do not need the and statement as the <=2500 bit has to be true  Register To Reply

3. ## Re: IF + If-And formula not calculating correctly Originally Posted by davsth perhaps
=IFERROR(IF(D3=0,0,IF(D3>2500,D3-(D3*\$G\$3%),IF(D3-(D3*\$H\$3%)<=100,-100,D3-(D3*\$H\$3%)))),0)

you do not need the and statement as the <=2500 bit has to be true
i tried your suggestion but unfortunately it does work.  Register To Reply

4. ## Re: IF + If-And formula not calculating correctly

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.  Register To Reply

5. ## Re: IF + If-And formula not calculating correctly

Hi,

Your formula is actually doing Exactly what your description says and is producing the correct results.

Take 133 as the Cost for example: Cost - 25% = 133-133*.25 = 133-33.25 = 99.75 (result is less than 100), so your formula gives you Cost - 100 = 133-100 = 33 This is correct according to your requirements (point C)
Take 134 as the Cost for example: Cost - 25% = 134-134*.25 = 134-33.50 = 100.5 (result is more than 100), so your formula gives you Cost - 25% = 134-134*.25 = 100.5 This is correct according to your requirements (point B)

If this is Not what you want as the result, then you need to re-think your requirements and logic to re-built the formula.

=IFERROR(IF(D3=0,0,IF(D3>2500,D3-D3*\$G\$3%,IF(D3-D3*\$H\$3%<=100,D3-\$F\$3,D3-D3*\$H\$3%))),0)  Register To Reply

6. ## Re: IF + If-And formula not calculating correctly

my solution: If(excel example.xlsx=true, "Fast and good results tailored to your needs", "lots of if's, but's then's and still no result")   Register To Reply