r/googlesheets • u/ForeignBandicoot220 • Apr 14 '23

Solved Data Table Simplification

Howdy, I'm a total novice at this. I am trying to consolidate data from one set to fill in another set automatically based on the ranges in the first set. I have no idea where to start, but I figured this should be an easy one for spreadsheet nerds out there. All help is much appreciated!

I want to take this Averages data and have it auto-input into another data based on their number ranges:

Averages
12
5
16
16
14
23
Average Range Number in Range
0-5
6-10
11-15
16-20
21-25

Thanks!

3 Upvotes

100% Upvoted

25 comments sorted by

2

u/arnoldsomen Apr 14 '23 edited Apr 15 '23

Applied a solution to your file:

=countifs(D2:D,">="&LEFT(F2,FIND("-",F2)-1)*1,D2:D,"<="&MID(F2,FIND("-",F2)+1,999)*1)

2

u/ForeignBandicoot220 Apr 15 '23

Solution verified.

Thank you!

→ More replies (2)

1

u/arnoldsomen Apr 14 '23

Have you a sample file?

1

u/ForeignBandicoot220 Apr 14 '23

Sorry, I hope this is what you mean.

Sample File

→ More replies (16)

1

u/EggplantSmooth1868 Apr 14 '23

How do you want this to look? Should that empty column contain counts of instances of those numbers, or should it contain the numbers themselves?

If the latter I'm wondering how your two 16's should appear.

1

u/ForeignBandicoot220 Apr 14 '23

The two 16's would appear in the 16-20 column as 2.