Averaging Ratios from a drop down list. Be gentle.. Amateur here..

[Pappz]

So I have 4 catagories for care provided to a disable adult, for accessing the community there could be a 1:1 staffing ratio, for personal care this could be 2:1. For group work this staffing ratio could be 1:10.


I need 4 areas to have a drop down selection of a a ratio. Then I need to generate an 'average' ratio that is rounded up to fit one of the following ratios 2:1, 1:1,1:2, 1:3, 1:4 ect to 1:10.


As you can probably tell I am an Excel amateur and I have survived thus far in my job by pure fluke.. however I really need to finish this spreadsheet so your help is much appreciated!

 

[Gerald Higgins]

Hi, welcome to the board.

The first stage in this is to forget about Excel for a minute, and explain to us the maths that you will be using to generate this average.

 

[shg]

Welcome to the board.

If all areas have equal weight, you could do it like this:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Ratios​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

Level​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

2/1​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Area 1​

[/td][td]

1/2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

1/1​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Area 2​

[/td][td]

1/2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

1/2​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Area 3​

[/td][td]

1/10​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

1/3​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Area 4​

[/td][td]

1/6​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

1/4​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

1/5​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Rounded-Up Avg​

[/td][td="bgcolor:#E5E5E5"]

1/3​

[/td][td]D7: =INDEX(A2:A12, MATCH(AVERAGE(D2:D5), $A$2:$A$12, -1))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

1/6​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

1/7​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

1/8​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

1/9​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

1/10​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[Pappz]

You seem to completely understand my problem however when I adapt your solution I doesn't recognise the formulae.

Welcome to the board.

If all areas have equal weight, you could do it like this:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Ratios​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

Level​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

2/1​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Area 1​

[/td][td]

1/2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

1/1​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Area 2​

[/td][td]

1/2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

1/2​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Area 3​

[/td][td]

1/10​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

1/3​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Area 4​

[/td][td]

1/6​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

1/4​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

1/5​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Rounded-Up Avg​

[/td][td="bgcolor:#E5E5E5"]

1/3​

[/td][td]D7: =INDEX(A2:A12, MATCH(AVERAGE(D2:D5), $A$2:$A$12, -1))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

1/6​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

1/7​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

1/8​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

1/9​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

1/10​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[Pappz]

Ok think its working but I'm struggling with formatting the cell. Tried formatting it as fraction but it changes 2/1 to 2. Is that avoidable? Id like it to stay as 2/1 (ideally to be viewed as 2:1 format)

Welcome to the board.

If all areas have equal weight, you could do it like this:

[Table="width:, class:grid"][tr][td="bgcolor:#C0C0C0"][/td][td="bgcolor:#C0C0C0"]

A​

[/td][td="bgcolor:#C0C0C0"]

B​

[/td][td="bgcolor:#C0C0C0"]

C​

[/td][td="bgcolor:#C0C0C0"]

D​

[/td][td="bgcolor:#C0C0C0"]

E​

[/td][/tr][tr][td="bgcolor:#C0C0C0"]

1​

[/td][td="bgcolor:#F3F3F3"]

Ratios​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][td="bgcolor:#F3F3F3"]

Level​

[/td][td="bgcolor:#F3F3F3"]

​

[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]

2/1​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Area 1​

[/td][td]

1/2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]

1/1​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Area 2​

[/td][td]

1/2​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td]

1/2​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Area 3​

[/td][td]

1/10​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

[/td][td]

1/3​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Area 4​

[/td][td]

1/6​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

[/td][td]

1/4​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

[/td][td]

1/5​

[/td][td][/td][td="bgcolor:#F3F3F3"]

Rounded-Up Avg​

[/td][td="bgcolor:#E5E5E5"]

1/3​

[/td][td]D7: =INDEX(A2:A12, MATCH(AVERAGE(D2:D5), $A$2:$A$12, -1))[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

8​

[/td][td]

1/6​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

9​

[/td][td]

1/7​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

10​

[/td][td]

1/8​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

11​

[/td][td]

1/9​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

12​

[/td][td]

1/10​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

 

[shg]

Format as ?/??

 

[shg]

That calculation seems like an oversimplification for any practical usage, though.