Amount Deduction by month

Dear community,

I would like to ask how do we build the formula that able to give us the output as below : The amount will deduct the balance for each until it fully utilise it. The remaining unutilised month will be zero.

I have also attached the working file for your reference.

Calculation.xlsx

Common Use Cases

Accepted answers

Qiu

@SH_94
We use a combination of Running Total and Multi-Row Formula tool as below.

1226-SH_94.yxmd

All comments

Qiu

@SH_94
We use a combination of Running Total and Multi-Row Formula tool as below.

1226-SH_94.yxmd

SH_94

Hi @Qiu ,

Thank you for the prompt response. May i know what is the function running total in this case?

Thanks again

Qiu

@SH_94
It is a Tool that will calculate the cumulative summ for specified column.

Below is from the sample flow page.

SH_94

Hi @Qiu ,

The multi row formula may not be work if i have the data as below

Thank you.

Calculation.xlsx

Qiu

@SH_94
Thank you for testing the flow.

Yes, for above case, since the "0" is not exsting, then [Balance] and [ RunTot_Quantity] for "0" row will be set to zero.

So instead of using "<" we should use "<=" instead.

1226-SH_94-r1.yxmd

SH_94

Hi @Qiu ,

Thank for the update. Just curious why the formula does not work on the below figures: After i add the total together , it does not give the same amount

Calculation.xlsx

Qiu

@SH_94
Like this ?

SH_94

Hi @Qiu ,

The amount column is correct as per your screenshot. May i know how u derive it?

Qiu

@SH_94
I only connected your new data to the revised workflow.

1226-SH_94-r1.yxmd

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335