Macro or a Formula that makes a calculation based on a MAX value between two fixed numbers

[Anne555]

Hello,
I need to create a macro or just a formula. I am not sure if a simple formula may be used instead of a macro. I don't have much experience with macros. If possible, please provide the whole code.

In a cell A1, there is an amount of 30000.

I need a formula or a macro that will allow to look up if the amount exceeds a predefined fixed amount of 10 000 and if so, takes a MAX value within a specific predefined range between two numbers 10 000 - 20 000. Then, deducts 10 000 (a fixed number) from a MAX value within that range. Then applies a fixed percentage of 11% on that amount and provides the result in the cell.

Example 1. Cell A1 has the amount is 30000. Then the calculation should be : (20000 - 10000) x 0.11= 1100
Example 2. Cell A2 contains the amount of 18000. Then the calculation should be : (20000-18000) x 0.11 = 220 In this case, 18000 is a Max value within a predefined specified range 10000 and 20000

Thank you in advance for your help

[Artik]

Assuming a maximum number in D1 and a minimum number in D2.
In B1:
French version

Please Login or Register  to view this content.

English version

Please Login or Register  to view this content.

Artik

[Anne555]

Thank you a lot for your help

There may also be that the value in A1 goes below that predefined range (because the values in the cell A1 may vary, as this is a total value of some other values).

Could you let me know how to integrate another IF argument to say that if the value goes below some minimum value, the result should simply be 0 ?


I tried to add one more IF condition inside the same formula -- IF(A1>20000,(20000-10000)*0.11,(A1-10000)*0.11,IF(A1<10000,0)), but strangely, it gives me an error message saying that there are too many arguments

Assuming a maximum number in D1 and a minimum number in D2.
In B1:
French version

Please Login or Register  to view this content.

English version

Please Login or Register  to view this content.

Artik

[Artik]

Please Login or Register  to view this content.

Artik

[Anne555]

Thank you a lot for your expert help.