Hi all,
I am working for an insurance company. I am working on a Excel sheet which totals up payments and statuses (Customer Cancellation, Bad Debt, Late Payment, etc.).
I can't come up with a function which checks for cancellation in the same column, gets the annual amount from a separate column but obviously on the same row, then divides that annual amount by 12.
Where cancellation is found, divide value by 12, then all the sums that are a result of the division to be added up (putting this final sum in the cell the formula is written, without spilling like the standard IF).
The problem I am trying to solve is that we have expected amounts for each month which we expect, but we want these expected amounts to minus the month direct debit payment where ever there is a "cancelled" entered in the column. Some customers have cancelled straight away without making a single payment, so I have to go to the annual column and divide by 12. I hope this makes sense, as it is hard to explain.
I could just do IF statements, but I then I have write another formula at the bottom of where the values have been spilled to sum where the values are greater than 0 (what the IF statement has returned). I am talking many months so I am trying to half the work if I can. I am hoping there is a function which do all this for me without having do another function just to then add up all the values.
Many thanks.
Craig
I am working for an insurance company. I am working on a Excel sheet which totals up payments and statuses (Customer Cancellation, Bad Debt, Late Payment, etc.).
I can't come up with a function which checks for cancellation in the same column, gets the annual amount from a separate column but obviously on the same row, then divides that annual amount by 12.
Where cancellation is found, divide value by 12, then all the sums that are a result of the division to be added up (putting this final sum in the cell the formula is written, without spilling like the standard IF).
The problem I am trying to solve is that we have expected amounts for each month which we expect, but we want these expected amounts to minus the month direct debit payment where ever there is a "cancelled" entered in the column. Some customers have cancelled straight away without making a single payment, so I have to go to the annual column and divide by 12. I hope this makes sense, as it is hard to explain.
I could just do IF statements, but I then I have write another formula at the bottom of where the values have been spilled to sum where the values are greater than 0 (what the IF statement has returned). I am talking many months so I am trying to half the work if I can. I am hoping there is a function which do all this for me without having do another function just to then add up all the values.
Many thanks.
Craig
