Formula to input a fixed value based on a fixed threshold over a variable trend

**Sophster** · 11-22-2011, 11:10 AM

Hello,

I have a spreadsheet with a variable growth trend over a fixed period of time and I need to add a fixed value for each 20% fill rate.

The IF formula does not work as there are too many scenarios considering the fill rate is a variable.

Is there any formula I can use so that the fixed value (in this case $3000) appear automatically on the correct year whenever the 20% threshold is passed?

The fixed value needs to appear at 20%, 40%, 60% & 80% fill rate.

I have attached an example to make it clearer.

Any help would be highly appreciated.

**6StringJazzer** · 11-22-2011, 11:33 AM

I looked at your description and your file. The information in your question is very sketchy, and the file doesn't seem to match what you described, which is "fixed value (in this case $3000) appear automatically on the correct year whenever the 20% threshold is passed". In your example I see no pattern to when the fixed value is shown.

Can you specify cell-by-cell logic as to what you want to happen?

**Sophster** · 11-22-2011, 12:33 PM

Sorry, I find it quite difficult to explain in a coherent manner. I don't know the formula so I manually entered the $3000 in the examples.

$3000 applies every time you pass 20%, so if 100% is lineary split across 5 years, you end up with 20% per year, this is then quite easy, $3000 applies for each year.

However, the % per year can vary, if the cumulative Year 1 is 40%, we need to account for 2*$3000, the next $3000 should only apply when the cumulative next reaches 60%.

I have included calculations in the attachment to clarify.