[Solved] Calculate first part at rate A, next part at rate B, rest at rate C

[andr3as]

Hello Forum,

i am a bit stuck on that topic.. i have an issue calculating the following:

given a number in a cell (e.g. 5) it should calculate in another cell:

• "the first 2" = 2*x*0.9
  "the next 2" = 2*x*0.4
  "the rest" = rest*x*0.2

examples:

lets say its 1:

1*x*0.9


lets say its 3:

2*x*0.9 + 1*x*0.4

lets make the 5 example explicit:

2*x*0.9 + 2*x*0.4 + 1*x*0.4

is this possible with a formula? if yes how? if not, is there another way?

[Zizi64]

What is the maximum value in the input cell?

[andr3as]

@Zizi64

thank you for your reply.. well, theoretically unlimited, practically it won't be much higher than 10 or 15..

however, as a heads up, it's not necessarily an integer.. it could as well be like 8.73 or something..

[keme]

=X*0.2+MIN(X;4)*0.2+MIN(X;2)*0.5

Substitute the cell address for X.

Or did I misunderstand?

[Zizi64]

well, theoretically unlimited, practically it won't be much higher than 10 or 15..

however, as a heads up, it's not necessarily an integer.. it could as well be like 8.73 or something..

And what are the expected results at these input numbers?

[MrProgrammer]

Hi, and welcome to the forum.

2*x*0.9 + 2*x*0.4 + 1*x*0.4

What you you mean by x?

given a number in a cell (e.g. 5) it should calculate in another cell:

"the first 2" = 2*x*0.9
"the next 2"  = 2*x*0.4
"the rest"    = rest*x*0.2

I am interpreting your post to indicate that the "number in a cell" is never negative.
=SUMPRODUCT(cell>{0;2;4};cell-{0;2;4};{0.9;-0.5;-0.2}) * x
[Tutorial] The SUMPRODUCT function, X23

it's not necessarily an integer.. it could as well be like 8.73 or something.

The formula above will calculate whatever x is times 3.546 [2×0.9 + 2×0.2 + 4.73×0.2].

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know