IF statements not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I am trying to work the following calculation which works for D2 <= 750,000, but as soon as I go above 750,000 the numbers start going out, e

750,000 should give 82
775,000 should give 837.5 but gives 85
1000,000 should give 950 but gives 1,075...... and so o

Am I going nuts!!! - can't see where I have gone wrong. I have pasted the function below

- Up to 150,000 (rate = 0.15%
- From 150k to 750K (rate = 0.10%)
- From 750k to 1.5mill (rate = 0.05%
- Anything above 1.5mill (rate = 0.025%

=IF(D2<=150000,(0.0015*D2),IF(750000<=D2>150000,(225)+(0.001*(D2-150000)),IF(1500000<=D2>750000,(825)+(0.0005*(D2-750000)),IF(D2>1500000,(1200)+(0.00025*(D2-1500000)),0)))

Regard
IanF
 
Ian,

Test backwards, top values first, like so

=IF(D2>1500000,225+600+375+(D2-1500000)*0.025%,IF(D2>750000,225+600+(D2-7500
00)*0.05%,IF(D2>15000,225+(D2-150000)*0.1%,D2*0.15%)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

IanF said:
Hi,

I am trying to work the following calculation which works for D2 <=
Click to expand...
750,000, but as soon as I go above 750,000 the numbers start going out, eg
750,000 should give 825
775,000 should give 837.5 but gives 850
1000,000 should give 950 but gives 1,075...... and so on

Am I going nuts!!! - can't see where I have gone wrong. I have pasted the function below:

- Up to 150,000 (rate = 0.15%)
- From 150k to 750K (rate = 0.10%)
- From 750k to 1.5mill (rate = 0.05%)
- Anything above 1.5mill (rate = 0.025%)
Click to expand...
=IF(D2<=150000,(0.0015*D2),IF(750000<=D2>150000,(225)+(0.001*(D2-150000)),IF
 
Hi
one way (not beautiful, but...)
=MIN(D2,150000)*0.0015+MAX(MIN(D2,750000)-150000,0)*0.001+MAX(MIN(D2,15
00000)-750000,0)*0.0005+MAX(D2-1500000,0)*0.00025

HTH
Frank

Hi,

I am trying to work the following calculation which works for D2 <=
750,000, but as soon as I go above 750,000 the numbers start going
out, eg

750,000 should give 825
775,000 should give 837.5 but gives 850
1000,000 should give 950 but gives 1,075...... and so on

Am I going nuts!!! - can't see where I have gone wrong. I have pasted
the function below:

- Up to 150,000 (rate = 0.15%)
- From 150k to 750K (rate = 0.10%)
- From 750k to 1.5mill (rate = 0.05%)
- Anything above 1.5mill (rate = 0.025%)
Click to expand...
=IF(D2<=150000,(0.0015*D2),IF(750000<=D2>150000,(225)+(0.001*(D2-150000
 
Back
Top