Excel - Dynamic output based on Min / Max Inputs

[Hunter Childress]

Please view the attached example.

I need help automate columns J - AN based on adjustable metrics. The goal for this is to be able to adjust the Min, Target, and Maximum, Bonus which will automatically update columns J - AN. The target is what I am achieve to hit. Anything more is a bonus, anything less is between the Min and Target.

The output in column J - AN would reflect the metrics. These metrics should dynamically change columns J-AN.

It's okay if there are different formulas on either side of the target.

Thank you in advance!!!

 

[AliGW]

Which version of Excel are you using?

 

[p45cal]

Try:
To replace current formula in H9 (=C9):
=SEQUENCE(,16,C9,(D9-C9)/15)
and delete the formula =D9 in cell W9

You can copy this formula down one cell to get the percentages.

In cell X9:
=SEQUENCE(,15,D9+(E9-D9)/15,(E9-D9)/15)
copy down. Delete the formulae in column AL

I do not understand how to incorporate the Bonus More than Target of 0.1.

 

[Hunter Childress]

Which version of Excel are you using?

I use office 365. I forgot to mention that I am hoping to resolve this with formulas only, no VBA.

 

[Hunter Childress]

Try:
To replace current formula in H9 (=C9):
=SEQUENCE(,16,C9,(D9-C9)/15)
and delete the formula =D9 in cell W9

You can copy this formula down one cell to get the percentages.

In cell X9:
=SEQUENCE(,15,D9+(E9-D9)/15,(E9-D9)/15)
copy down. Delete the formulae in column AL

I do not understand how to incorporate the Bonus More than Target of 0.1.

Thank you!!! This works great. I forgot about the Sequence formula.