Amit Punia
Member
I've attached the file for referencing.
Parts of the Table
Column A(Names) - These are the names.
Column B(Used) - It provides the data, how much any particular Name used it.
Column C(Cumulative Used) - This shows the cumulative use of any name based on Column B.
Column D(Free Units) - This tells that these many units are free by any particular Name and amount is only chargeable when any name exceed Free Units number.
Column E(Net Free Cumulative) - This shows whether any name exceeded the free units and if yes by how much cumulatively.
Column F (Net Free Instance) - This shows whether any name is exceeding on a single instance and by how much.
Column G (Output) - Need a formula in this cell.
Example
For Name A (From Raw Data Sheet)
In cell B2 and C2, Name A used 3 but it's less than the Free Units in cell D2 i.e. 8. Nothing will be chargeable if column C is less than column D for any particular name.
In cell C5, Name A cumulatively used 5 but it's still less than the Free Units in cell D5 i.e. 8.
In cell C9, Name A cumulatively used 9 and it's more than the Free Units in cell D9 i.e. 8.
In cell C13, Name A cumulatively used 10 which is already more than the Free Units.
Need a formula in G Column which tells if any name exceeded the free units or not. The only requirement in this is, it should tell, say for Name A, that cumulatively Name A is exceeded the Free Units in C9 cell. All the cells before that, the used amount was below the free units. The other part is of the formula is if in the subsequent cells (Like in Cell C13) if the cumulative count is more than the Free Units, the formula should treat it as all those cases when it was below the Free Units. So if the cumulative column is less than free units, Column G should show value 0(Cell G2 and G5 should show 0). If for only that instance where the value is exceeding free units for the first time, it should show value 1(Cell G9 should show 1). In all the subsequent cases where the cumulative used exceeds the Free Units, Column G should show value 0 again(Cell G13 should show 0).
A table is prepared in the excel file to show how exactly the data will look like after the output. Kindly check "Output" sheet and Column G in that sheet is the output required through formula.
Let me know if there is any further query/clarification.
Thank you so much in advance.
Parts of the Table
Column A(Names) - These are the names.
Column B(Used) - It provides the data, how much any particular Name used it.
Column C(Cumulative Used) - This shows the cumulative use of any name based on Column B.
Column D(Free Units) - This tells that these many units are free by any particular Name and amount is only chargeable when any name exceed Free Units number.
Column E(Net Free Cumulative) - This shows whether any name exceeded the free units and if yes by how much cumulatively.
Column F (Net Free Instance) - This shows whether any name is exceeding on a single instance and by how much.
Column G (Output) - Need a formula in this cell.
Example
For Name A (From Raw Data Sheet)
In cell B2 and C2, Name A used 3 but it's less than the Free Units in cell D2 i.e. 8. Nothing will be chargeable if column C is less than column D for any particular name.
In cell C5, Name A cumulatively used 5 but it's still less than the Free Units in cell D5 i.e. 8.
In cell C9, Name A cumulatively used 9 and it's more than the Free Units in cell D9 i.e. 8.
In cell C13, Name A cumulatively used 10 which is already more than the Free Units.
Need a formula in G Column which tells if any name exceeded the free units or not. The only requirement in this is, it should tell, say for Name A, that cumulatively Name A is exceeded the Free Units in C9 cell. All the cells before that, the used amount was below the free units. The other part is of the formula is if in the subsequent cells (Like in Cell C13) if the cumulative count is more than the Free Units, the formula should treat it as all those cases when it was below the Free Units. So if the cumulative column is less than free units, Column G should show value 0(Cell G2 and G5 should show 0). If for only that instance where the value is exceeding free units for the first time, it should show value 1(Cell G9 should show 1). In all the subsequent cases where the cumulative used exceeds the Free Units, Column G should show value 0 again(Cell G13 should show 0).
A table is prepared in the excel file to show how exactly the data will look like after the output. Kindly check "Output" sheet and Column G in that sheet is the output required through formula.
Let me know if there is any further query/clarification.
Thank you so much in advance.
