Averaging Total across variable # of cells, repeatedly

[yousifH]

Hey everyone!

Trying to figure out this calculation and got a bit stuck. Please see the image below.

So basically, we received that $500K on 11/4/22 after 4 (see the count below the dollars) days of no pay. I need to average out the cost (500k / 4 days = 125k per day), and display the result across each of the 4 days. For example, 11/1/22, 11/2/22, 11/3/22, and 11/4/22 should all have 125k under each. Similarly, we received 250k on 11/7/22 and we need that averaged (250k/3 days) and spread across 11/5, 11/6 and 11/7. Hopefully this makes sense, let me know if you have any questions. Thanks!

 

[ekrause]

Making some pretty big assumptions in your file to work with this Formula. Assume a 1 will always follow a day of payments in row 3. Assume you will continue the timeline horizontally - e.g. Dec 1 either starts a new row with a 1 or would be in column AE.

You can lock the bolded sections in the formula but only after the :

Formula starting in cell A4 =IFERROR(SUM(OFFSET(A2,0,0,1,MATCH(1,B3:Y3,0)))/MAX(OFFSET(A3,0,0,1,MATCH(1,B3:Y3,0))),0)

A5 is the formula text from cell A4. Good luck hope this helps!

 

[yousifH]

Works perfectly, thank you!