SUM to threshold and report adjacent cell

[prairiebliss]

Hi there,

I'm wondering if this can be done, I have spend several days looking up formulas but I just don't have enough knowledge to make the formulas do what I want to do.
I need to find values associated with the 25th, 50th, 75th percentiles. Because of the format of my data I need to sum the data until it is as close to the percentile as possible, then report the value of the adjacent column.

Sorry, I'm not sure what the best formatting is, but here is an example.. The rows are incomplete, as they do not equate 100% but hopefully you get the idea...

A1-Size Class	B1-Sample 1 %	C1-Sample 2 %
1	12.81	15.65
2	12.52	7.92
3	7.64	2.85
4	3.54	5.24
5	3.62	4.68

And this is how I would like to present my results, with what the results of the above data should be...

A84 - Percentiles (%)	B84 - Class at %	C84 - Class at %
25	2	3
50
75

Thank you so much for your time, I really appreciate it. Let me know if there is anything that I could present better.

 

[vletm]

prairiebliss
You should upload a sample Excel-file here.

 

[prairiebliss]

I've uploaded a sample sheet as requested. Thank you!

 

[vletm]

prairiebliss
I can guess ... before someone else give something else ...
Ranges F5:F7 and I5:I7 could be Your formulas to solve 'percentiles' ... modify those as needed
After that ... press [ Solve ]-button to get the most near 'percentiles'-values 'size class'es by red fonts.
If something like this then I could modify needed layout.

Note: My results are different that Your given (from then bottom of Your data (( which I move more right ))
Ideas?

 

[prairiebliss]

Thanks!
Unfortunately our Admins have macros disabled in Excel so I can't view it.

 

[vletm]

prairiebliss
'my results' are as below
Green as percentiles
Red as the most near 'percentiles'-values 'size class'es

I could do same with formulas ... but not so ... way.