How to change a value based on cumulative total of another column (using Excel formula)

[shonal]

We provide funds in smaller tranches and need to record expense against that.
In the image below, rows 5 and 6 show income in AUD and USD and their respective FX rates.

These are then spent in USD from Row 13 down. In column I, I want the first FX rate (I5) to be used to calculate AUD up to USD50,000 (cell H5) and then exchange rate to change to I6 from cell H32 down. The first FX rate doesn't need to stop at exactly USD50,000 but can stop where it crosses the USD50,000 mark

Will appreciate your assistance on this. Thanks

 

[SanjayGMusafir]

We provide funds in smaller tranches and need to record expense against that.
In the image below, rows 5 and 6 show income in AUD and USD and their respective FX rates.

These are then spent in USD from Row 13 down. In column I, I want the first FX rate (I5) to be used to calculate AUD up to USD50,000 (cell H5) and then exchange rate to change to I6 from cell H32 down. The first FX rate doesn't need to stop at exactly USD50,000 but can stop where it crosses the USD50,000 mark

View attachment 70027

Will appreciate your assistance on this. Thanks

Try this in Cell J13 and fill it down to test if it is working the way you want it to -

=IF(SUMIFS($H$13:$H$300,$A$13:$A$300,A13)>50000,SUMIFS($H$13:$H$300,$A$13:$A$300,A13)*$1$6,SUMIFS($H$13:$H$300,$A$13:$A$300,A13)*$1$5)

Further may I suggest to update your Excel Version and platform in Profile - Solution can vary depending upon the version.