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

Pappz

New Member
Joined
Sep 30, 2016
Messages
3
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!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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.
 
Upvote 0
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]
 
Last edited:
Upvote 0
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]
 
Upvote 0
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]
 
Upvote 0
That calculation seems like an oversimplification for any practical usage, though.
 
Upvote 0

Forum statistics

Threads
1,226,693
Messages
6,192,463
Members
453,725
Latest member
cvsdatreas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top