Need Excel help

[Hurricane]

This shouldn't be too hard, but I couldn't think of a good solution for this problem (see screenhot)

I have a table, sorted by column B. In column A are values, and with the help of the SUBTOTALS function I have calculated the average values for each code. The problem is that there are some empty cells in column A. I want to fill the empty cells with the average value for that code. That is, cell A4 and A12 should get value 403.0409, and cell A20 and A21 should get 435.6067. How do I do this (I need to do this in order to export the file into another program correctly)?

 

[Civddict]

you can use a) average function- read help for explanation how to use it
b) there is place on right corner of excel status bar: when you select two or more cells, it gives you sum of them. if you rgiht click on that place, you can chose to show average instead of sum

 

[Hurricane]

Originally posted by Civddict
you can use a) average function- read help for explanation how to use it
b) there is place on right corner of excel status bar: when you select two or more cells, it gives you sum of them. if you rgiht click on that place, you can chose to show average instead of sum

Uh, yes. This is what I do with the SUBTOTALS function. I have already calculated the average. This is not the problem. The problem is that I want to fill the empty cells with that average value.

I figure the easiest way would be to use the C-column and put some formula on every row, which would look something like this: =if(a1>0;a1;average_for_this_code). The problem is I don't know how to write the "average_for_this_code" part, bacuse the average value it needs to use will be in a different relative position for each row.

 

[Black Fluffy Lion]

I don't totally understand what you mean by 'average_for_this_code', is that the average of every block, e.g. AVERAGE(B1:B13)??

If your problem is that the references change when you copy the formula down, how about using $...

=IF(A1>0;A1;$A$14)

 

[ainwood]

Hurricane,

Use the SumIf() and CountIf() funcitons to calculate the average based on the code.

 

[Hurricane]

Thanks guys for your input. I found a solution.

First I made a formula in column H to mark if that row shows the average value or not. The formula for this is a simple

=IF(RIGHT(B9)="e";1;0)

By using this row, I can get the relative position of the average value for that product code. If you look at my new screenshot, you see that cell I3 uses the value found in cell A5 (two rows down), while the value in cell I18 uses the average found 5 rows down. For that I use this formula:

=IF(ISBLANK(A12);OFFSET(A11;MATCH(1;H12:H722;0);0);A12)

After doing this I can simply copy&paste the values in column I into column A (using paste special->values) and get a column with no empty values.