Changing Chart data series

  • Thread starter Thread starter Anne
  • Start date Start date
A

Anne

Hi all! I was wondering if there was a way to create a
chart from data that is different from month to month. I
have a summary page that I want to create a chart from,
but I import the data and the number of rows varies every
month. Some months I will have 4 rows of data and the
next month I will have 25 rows of data.

Here is an example of what I am trying to do:
Column A is to be used for percentage calculations
Column B has descriptions in it (all text)
Columns C, D, and E are not involed in the summary
Column F has numeric values in it. It is used for cost
for the description in Column B.

A B F
xyz Company 12000.00
abc Company 24000.00
def Company 12000.00
48000.00 (total)

I want column A to be a percentage taking F1 and dividing
it by F4 in this example or the last row if there is more
data, F2/F4(or last row) and so on. I can do this fine
if my number of rows didn't change from month to month.
Are there any suggestions on a way to do this? Should I
use the LastRow Variable? If so, how do I get it to work
for my situation?

Thanks in advance!
Anne
 
A B F
xyz Company 12000.00
abc Company 24000.00
def Company 12000.00
48000.00 (total)

I want column A to be a percentage taking F1 and dividing
it by F4 in this example or the last row if there is more
data, F2/F4(or last row) and so on. I can do this fine
if my number of rows didn't change from month to month.
Click to expand...

Let's assume there is nothing else on this sheet. In cell A1 of your
example, enter the following formula:

=F1/OFFSET($F$1,COUNT(F:F)-1,0)

Now copy that formula down to A3.
 
Back
Top