Which formula?

[LisaVelthuizen]

I am making a calculator for my website (i make the calculator in the program excel), but i don't know which formula i must take and how to fill it in.

I have 2 things i don't know, the first formula is:

- By input of numbers lower, or equally to 200.000, the answer must be 725.(So input 50.000, 70.000, 150.000 it doesn't matter, has the same answer: 725. With every 1.000 that comes up on the 200.000, the answer increases with 100. In numbers:

Input: ≤ 200.000 -> answer = 725
201.000 -> answer = 825
202.000 -> answer = 925
etc.......

- my second formula I think is more complicated, but has the same structure as the one above.

Input: ≤30.000 --> the answer must be 800.

between 30.000 and 60.000 -> every 1.000 upon the 30.000, but till an input of 60.000 --> the answer increases with 19,5 upon the first 800. So 60.000 must have the answer 800+ (30*19.5) = 1.385

Between 60.001 and 100.000 -> every 1.000 upon the 60.000, but till an input of 100.000 --> the answer increases with 15,60 upon the 1.385. So 100.000 must have the answer 800+ (40*15.60) = 2.009.

This principle continues. In numbers:

Input: Output:
≤30.000 -> answer = 800
30.001 - 60.000-> per 1.000 upon the input, the answer increases with 19.50 above the first 800.
60.001 - 100.000 > per 1.000 upon the input, the answer increases with 15.60 above the first 800+19.50 per 1.000(see line before).
100.001 - 200.000 -> per 1.000 upon the input, the answer increases with 7.80 above the first 800+19.50 per 1.000+ 15.6 per 1.000(see line before).
200.001 - 500.000 -> +5.85 per 1.000 ( but again above the first answers)
500.001 - 1.000.000 -> + 4.875 per 1.000 (but again above the first answers)
1.000.001 and higher -> +2925 per 1.000 (but again above the first answers)

Can anybody help me to find these 2 formulas?????

 

[Gerald Higgins]

Re: Wich formula?

Hi, welcome to the board.

This formula will give you the results you say you want, for the first problem . . .

=725+MAX(0,ROUNDUP((A1-200000)/1000,0)*100)

Assumptions:
1) The input number is in A1.
2) If the input value is 0, you want to return 725. If this is not the case, there are simple ways round this.

Does this work for you ?

 

[LisaVelthuizen]

Re: Wich formula?

Hi Gerald,

Thankyou for your reaction!

If the input is 0, the answer also must be 0....

From input 1 till 200.000 the output must be 725..

 

[Gerald Higgins]

Re: Wich formula?

So maybe this ?

=((A1>0)*725)+MAX(0,ROUNDUP((A1-200000)/1000,0)*100)

 

[LisaVelthuizen]

Re: Wich formula?

Yes great, this one works.

i fixed it to: =((A1>0)*725)+MAX(0,ROUNDUP((A1-200000)/100000,0)*100).

Thanks!!

Do you also have a suggestion for my second formula?

 

[Gerald Higgins]

Re: Wich formula?

Your "fixed" version of my formula returns a result of 825, if A1 contains 202,000.

But in your OP, you said the result in this case should be 925, which is what my formula gives you.

What's happening here ?