Excel 2010 Average of Percentages 1

[PWD]

Good morning, my boss has decided that creating an average of monthly percentage cells is incorrect and I've got to sum the enumerators and divide this by the sum of the denominators. I'd like to be able to get the individual figures from each cell to save me clicking each cell and noting each number etc. etc. I suppose I could FIND the / and work from there. Does that sound like a plan?

Many thanks,
D€$

 

[PWD]

Dang, that just looks at the results, e.g. 0.964535046076515 instead of the formula =3454/3581

Many thanks,
D€$

 

[PWD]

Hmm. Add 3 rows under my data row, 17

Copy the formulae to row 18

Replace = with nothing

In row 19 put =LEFT(I18,(FIND("/",I18))-1)

In Row 20 put =RIGHT(I18,(LEN(I18)-(FIND("/",I18))))

Copy the results and PasteAs values

Convert the results to Number

In the target, result, cell use =SUM(I19:O19)/SUM(I20:O20)

That still doesn't really help as I don't end up what I'd really need to be able to add subsequent months, e.g.

=(13908+15753+18774+20318+17988+19701)/(14704+15902+18953+20557+18213+20141)

Grrr.

Many thanks,
D€$

 

[SkipVought]

Hi,

Each numerator and denominator VALUE has been CALCULATED from SOMEWHERE. Forget finding a "/". Go to the source.

Maybe you could upload your workbook or a representative subset.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

Hi Skip, unfortunately these figures come from other workbooks that I have manually typed in.

I have now added a further couple of rows:

Cell I21
=I19 & "+" & J19 & "+" & K19 & "+" & L19 & "+" & M19 & "+" & N19 & "+" & O19

Cell I22
=I20 & "+" & J20 & "+" & K20 & "+" & L20 & "+" & M20 & "+" & N20 & "+" & O20

I copied and pasted them as values so I could then build up the required % formula. This is only a one-off exercise to catch up with the financial months so far - the next months' data will just be tacked on to the end of each formula as we report subsequent months. I've attached part of the report here.

Many thanks,
D€$

 

[SkipVought]

...these figures come from other workbooks...

So why "type them in"???

I'll check out your wb.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Plz upload again. Seems there's a problem with the upload file.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Here's a function that makes it easy to plug into your sheet: 0 for numerator 1 for denominator...

Function WhatND(r As Range, iND As Integer)
'SkipVought 2016 NOV 17
'first argument: cell range
'second argument: 0 Num, 1 denom
    If r.Value = "" Then
        WhatND = 0
    Else
        Select Case iND
            Case 0, 1
                WhatND = Split(Right(r.Formula, Len(r.Formula) - 1), "/")(iND)
                If IsNumeric(WhatND) Then
                    WhatND = WhatND * 1
                End If
            Case Else
                WhatND = 0
        End Select
    End If
End Function

Use formulas (Num column and Denom column) in structured table and they propagate to new rows as data is added. Then =Sum(Num)/Sum(Demon) gets you your cumulative average.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Example

BTW,

You said, "Hmm. Add 3 rows under my data row, 17"

I never, well, hardly ever, add summary rows "under my data" cuz my data most often grows and why would anyone, in the days of electronic spreadsheets, put summary data in an obscure place that forces a user to hunt and search for the ultimate result of the entire enterprise?

PUT IT UP TOP!


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PWD]

Thanx Skip, that's really good!!! I had a go before looking at your workbook though, so I could understand it. "My First UDF"

I still need to get the individual cell values for each iND value in a SUM() formula so I can add to them next month. It's a real pain considering it's making less than +-0.5% difference compared with the Average of the existing % cells. Oh well, you've helped me, a) learn something & b) saved me a few steps.

I've uploaded a new test file where I used the above formulae for Cells I21 &I22 then copied and pasted as values.

Many thanks,
D€$