Progessive Discounts Table

[paulomichels]

Hello People, i'm new here and i'd like to ask help for you.
I have a sheet where I want to calculate de progressive discount based on the more expensive and progressively.

It works Like this: when the patient will do a surgery (3 or 4) he will pay the 100% of more expensive procedure, 50% from de second more expesive and 30% of the third more expensive.
But, the discounts are different between the hospitals and the anesthesia team.

I'd like to run the table, put an "X" on the dark blue, or a selector button (whichever is easier), and the Excel calculate all for me in the box on the left.

The yellow Hospital (A), and the Green Hospital (B) charges 100% on the more expensive, 50% on the second more expensive and 30% on the third more expensive
The Blue hospital (C) charges 100% on the more expensive, 100% on the second more expensive, and 50% on the third more expensive.
And the Anesthesia Team (gray) charges 100% from the more expensive, 60% from the second more expensive and 40% from the third more expensive.

Is it possible to only put an "X" in the front of the right table, or a selector button, and the excel do the rest for me?

Thanks!!!

[GC Excel]

Hi and welcome to the forum,

Here's a suggestion in the attached file.
I've used different helper columns for this calculation.

[paulomichels]

Hi and welcome to the forum,

Here's a suggestion in the attached file.
I've used different helper columns for this calculation.

Thanks for Help; but didn't work.
In My excel, when i change de "X"from place, nothing works...
something that i made wrong??

thanks!

[GC Excel]

Maybe you have the calculation mode set to manual... Try by pressing F9 to see if the results will recalculate.
Also, please use this new version attached, there was a problem no selection was made.

[paulomichels]

Thanks! This is exactly what I need. Thanks a lot!!!
But, I'd like to learn together, not just ask help.
I Tried from a different point of view. In the constructor form i get the correct answer, but in the sheet appears an error "#NUM".
What is my error here??

I tried to simplify the process putting all the formula in one space only. Is it possible?

[GC Excel]

Hi,
The formula you use are correct but since you are evaluating a range of cells, it's an array formlua and you have to validate them by using CTRL+SHIFT+ENTEr
so go in D10, press F2 then CTRL+SHIFT+ENTER
Repear for the 4 formulas.

[paulomichels]

hi,
the formula you use are correct but since you are evaluating a range of cells, it's an array formlua and you have to validate them by using ctrl+shift+enter
so go in d10, press f2 then ctrl+shift+enter
repear for the 4 formulas.

thanks!!!!!!!
It's exactly what i need!!!!
Thank you very, very much!