Hi all,
I have an Excel conundrum I can't seem to crack.
In Column A I have a long list of counties in a spreadsheet, and each of those counties is a member of a larger group of counties that pertain to a particular funding request to our organization. In column B is the total funding amount in that request--i.e. if an organization received $3,000 and claims to serve counties Smith, Robeson, Macon, and Duplin, the spreadsheet looks like this:
A1 Smith B1 3000
A2 Robeson B2 3000
A3 Macon B3 3000
A4 Duplin B4 3000
.. and the list goes on with counties from other requests, for about 1200 total row entries.
I'm trying to set up a function that allows the total amount for each particular request group (e.g., 3000) to be divided up by the number of cells *up until* the B value changes. So, if the next B value is 6500, for example, the formula would only count A1-A4 in the denominator. This would theoretically allow me to drag the formula across the entire column, as opposed to manually changing the range every time there's a new grouping of counties. (The entries have already been ordered so that no two groupings of counties from a same-value request (i.e. two 10,000 value requests) are adjacent to each other).
I've been using COUNT formulas, such as B1/(COUNT(B1:B4)) to get the values I need, but this strategy requires me to change the range manually. I have not been able to construct a COUNT formula that can account for an unknown range based on a value *change* rather than occurrence. I would appreciate any help you could provide!
I have an Excel conundrum I can't seem to crack.
In Column A I have a long list of counties in a spreadsheet, and each of those counties is a member of a larger group of counties that pertain to a particular funding request to our organization. In column B is the total funding amount in that request--i.e. if an organization received $3,000 and claims to serve counties Smith, Robeson, Macon, and Duplin, the spreadsheet looks like this:
A1 Smith B1 3000
A2 Robeson B2 3000
A3 Macon B3 3000
A4 Duplin B4 3000
.. and the list goes on with counties from other requests, for about 1200 total row entries.
I'm trying to set up a function that allows the total amount for each particular request group (e.g., 3000) to be divided up by the number of cells *up until* the B value changes. So, if the next B value is 6500, for example, the formula would only count A1-A4 in the denominator. This would theoretically allow me to drag the formula across the entire column, as opposed to manually changing the range every time there's a new grouping of counties. (The entries have already been ordered so that no two groupings of counties from a same-value request (i.e. two 10,000 value requests) are adjacent to each other).
I've been using COUNT formulas, such as B1/(COUNT(B1:B4)) to get the values I need, but this strategy requires me to change the range manually. I have not been able to construct a COUNT formula that can account for an unknown range based on a value *change* rather than occurrence. I would appreciate any help you could provide!
