K
KC Rippstein
I have a report where I pull in revenue posted by month for the current calendar year from another Excel worksheet. Jan gets posted into column AA...Dec in AL. I use sumproduct because I want it to match the client code and the month.
I need to annualize the amount we have received year-to-date to compare with our budgeted income for that client.
I had tried this array formula, where column A is just the name of our client:
=IF($A4="","",AVERAGE(IF($AA4:$AL4<>0,$AA4:$AL4,""))*12)
This is close to what I want but has two fundamental flaws:
1) In column R, I have the effective date that we took the client over. This would only come into play if we have a new case effective later than 1/1 of the current year. I really need my "average" formula to start on the correct month (Jan in most cases, but possibly a later month as shown in column R).
2) I really should be finding the last month for which we actually received commissions. I was ignoring zeroes to accomplish this, but this also ignores the zeroes that occur in the middle months, which is not an accurate average.
So my average needs to test the year of R4 and use its month as a starting point in some cases, otherwise use January as the starting point. Then it needs to include all months from that starting point to the last month with a revenue posted (this final month can vary row by row). The revenue is applied to the month it is supposed to cover, not to the month we received the check, so this should work.
Thank you for any assistance you can offer. If I should not be using an array formula, I'd love to remove it and speed up my worksheet. I really just used it as a last resort.
KC Rippstein
I need to annualize the amount we have received year-to-date to compare with our budgeted income for that client.
I had tried this array formula, where column A is just the name of our client:
=IF($A4="","",AVERAGE(IF($AA4:$AL4<>0,$AA4:$AL4,""))*12)
This is close to what I want but has two fundamental flaws:
1) In column R, I have the effective date that we took the client over. This would only come into play if we have a new case effective later than 1/1 of the current year. I really need my "average" formula to start on the correct month (Jan in most cases, but possibly a later month as shown in column R).
2) I really should be finding the last month for which we actually received commissions. I was ignoring zeroes to accomplish this, but this also ignores the zeroes that occur in the middle months, which is not an accurate average.
So my average needs to test the year of R4 and use its month as a starting point in some cases, otherwise use January as the starting point. Then it needs to include all months from that starting point to the last month with a revenue posted (this final month can vary row by row). The revenue is applied to the month it is supposed to cover, not to the month we received the check, so this should work.
Thank you for any assistance you can offer. If I should not be using an array formula, I'd love to remove it and speed up my worksheet. I really just used it as a last resort.
KC Rippstein