Problem with % increase based on certain date

[Farhasnat]

I have monthly price calculation sheet for few years and on cell E9 I have put a 3% annual price increase for doctor's fee and on cell F9 i have put the date from when I price increase should pick up.

Now, I have used the following formula to =MAX(IF(I6=1,$H$9*((1+($E$9))^((I3-$F$9)/365)),H9),1) to sort of show the value/factor of price increase

My base doctor fee is 500 which is shown in row 12 and on row 13 I multiply base price with price increase factor on row 9.

The problem is ---- with this formula although the price increase should be 3% annually starting 2025 but I see only 0.02% increase starting 2025 and the full 3% price increase is picks up on 2026.

Any help to correct the formula so that the price increase capture full 3% starting 2025 would be great help

[HansDouwe]

Please put all the desired price increases in 1 table and try this formula in H14 and copy across:

Please Login or Register  to view this content.

[oeldere]

With a table structured data.


F1=VLookup(A2,$S$1:$U$5,3,1)

See the attached file.

[Farhasnat]

With a table structured data.


F1=VLookup(A2,$S$1:$U$5,3,1)

See the attached file.

Thank you for the wonderful solution. However, my sheet has lots of price % increase rows for other items. Is there is any way I could one line formula just the way I did in my example ? Or anything I am doing wrong with the formula I provided in the example ?

Thank you once again

[oeldere]

my sheet has lots of price % increase rows for other items.

I don't see any other items in your example file.

In that case we don't know that and can't consider that in our offered solutions.



I suggest to use a table Hans Douwe suggest to get the result.

[HansDouwe]

my sheet has lots of price % increase rows for other items

Are these other price increases at other times?
Or are they the same percentage price increases at the same times, but on different products?

[HansDouwe]

If there per product different price increases please put all price increases and base prices of the products in 2 separate tables and try this formula, copy down and across:

Please Login or Register  to view this content.

[oeldere]

I guess differant products changes on differant times, with differant %.

If so, i would choose to use the table structurered and expand the VLookup table.

[HansDouwe]

I agree with Oeldere that it is advisable to record the data in a structured way. Then the whole is more manageable.

The solution in post #7 also works with structured tables and also assumed different products changes on different times, with different %.

[Farhasnat]

Thank you all for resourceful help. I would have to figure out another way as this is financial model and won't really look nice to put in a table which is why I used a one line formula which is working fine just that the price increase starts one year later from the date in reference

[oeldere]

I would have to figure out another way as this is financial model

If you would be able to use Pivot Table to analyse the data (financial model), I strongly suggest to use a table structuctured data.

[HansDouwe]

This solution does not add new tables and works with the current structure.

Please try in H9 and copy across:

Please Login or Register  to view this content.

[Farhasnat]

@HansDouwe Thank you so much !!!! You saved my life