grab value from table with < > range

[overbomb]

Hi

I have a table per below from which I would like a formula to grab the % value which corresponds to the Days

e.g. I have another calc which provides me with 185 days. I would like to create a formula/adjust my table (whatever is necenssary) so that I can write a formula which will look at the table and identify that 85 days, equates to 50%

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"] Days [/TD]
[TD="class: xl64, width: 64"] [/TD]
[/TR]
[TR]
[TD="class: xl65"] 0>31 [/TD]
[TD="class: xl66"] - [/TD]
[/TR]
[TR]
[TD="class: xl67"] 31>60 [/TD]
[TD="class: xl68"] - [/TD]
[/TR]
[TR]
[TD="class: xl67"] 61>90 [/TD]
[TD="class: xl68"] - [/TD]
[/TR]
[TR]
[TD="class: xl67"] 91>120 [/TD]
[TD="class: xl69, align: right"]20%[/TD]
[/TR]
[TR]
[TD="class: xl67"] 121>150 [/TD]
[TD="class: xl69, align: right"]30%[/TD]
[/TR]
[TR]
[TD="class: xl67"] 151>180 [/TD]
[TD="class: xl69, align: right"]40%[/TD]
[/TR]
[TR]
[TD="class: xl67"] 181>210 [/TD]
[TD="class: xl69, align: right"]50%[/TD]
[/TR]
[TR]
[TD="class: xl67"] 211>240 [/TD]
[TD="class: xl69, align: right"]60%[/TD]
[/TR]
[TR]
[TD="class: xl67"] 241>270 [/TD]
[TD="class: xl69, align: right"]70%[/TD]
[/TR]
[TR]
[TD="class: xl67"] 271>300 [/TD]
[TD="class: xl69, align: right"]80%[/TD]
[/TR]
[TR]
[TD="class: xl67"] 301>330 [/TD]
[TD="class: xl69, align: right"]90%[/TD]
[/TR]
[TR]
[TD="class: xl70"] 331>365 [/TD]
[TD="class: xl71, align: right"]100%[/TD]
[/TR]
</tbody>[/TABLE]

Any help much appreciated

Many thanks, Maddy

 

[Joyner]

Hello Maddy, sorry but I am not quite following what you need. Your table is in 30 days groups, what do you mean "identify that 85 days, equates to 50%"

What result do you want from the table above?

 

[overbomb]

Hello Maddy, sorry but I am not quite following what you need. Your table is in 30 days groups, what do you mean "identify that 85 days, equates to 50%"

What result do you want from the table above?

Thanksforbgetting back to me

Apolgies, I meant to type 185 days

so for example, if the result comes out at 185 (from a different exercise) then I'd like write a formula to search the range in the first column which would then identify the corresponding percentage of 50%

does that make sense/any clearer?

Thanks, Maddy

 

[Joyner]

Okay, so you want to look in the first column, find where the number of days (in this case 185) falls, then return 50%?

 

[Joyner]

If you want the above, this will return the % for the number of days, assumes days start in A2 and % in B2:

=LOOKUP(185,--(LEFT(A2:A13,SEARCH(">",A2:A13)-1)),B2:B13)

 

[overbomb]

thank you for your help - it worked perfectly

I have a second question..

currently I have an index/match formula pulling a % from a table. However, I want to build a range into this formula and despite a few attempts - I can't quite manage it..
[TABLE="width: 415"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Grade</SPAN>[/TD]
[TD]1>1.99</SPAN>[/TD]
[TD]2>2.99</SPAN>[/TD]
[TD]3>4[/TD]
[TD]</SPAN>[/TD]
[/TR]
[TR]
[TD]Employee</SPAN>[/TD]
[TD="align: right"]0%</SPAN>[/TD]
[TD="align: right"]1%</SPAN>[/TD]
[TD="align: right"]3%</SPAN>[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Employee1</SPAN>[/TD]
[TD="align: right"]0%</SPAN>[/TD]
[TD="align: right"]1%</SPAN>[/TD]
[TD="align: right"]5%</SPAN>[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Employee2</SPAN>[/TD]
[TD="align: right"]0%</SPAN>[/TD]
[TD="align: right"]2%</SPAN>[/TD]
[TD="align: right"]8%</SPAN>[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Employee3[/TD]
[TD="align: right"]0%</SPAN>[/TD]
[TD="align: right"]2.5%</SPAN>[/TD]
[TD="align: right"]1%</SPAN>[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Employee4</SPAN>[/TD]
[TD="align: right"]0%</SPAN>[/TD]
[TD="align: right"]1.5%</SPAN>[/TD]
[TD="align: right"]5%</SPAN>[/TD]
[TD="align: right"][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL span=4></COLGROUP>[/TABLE]


For example, Employee2 achieved a score of 2.79, from the table above this would equate to the percentage value of 2%. I would like to write a formula, similar in principle to the last one, which will pull the corresponding % value from the table based on score within the range.
At the moment I am using an index/match but it will only pick up an absolute values, not the range

I can't figure out how to tackle it..

Do you have any ideas?

Thanks, Maddy

If you want the above, this will return the % for the number of days, assumes days start in A2 and % in B2:

=LOOKUP(185,--(LEFT(A2:A13,SEARCH(">",A2:A13)-1)),B2:B13)

 

[Joyner]

Hello Maddy, I think you are looking for something like this, assumes your table in in range A1:D:6

=INDEX(A1:D6,MATCH("Employee2",$A$1:$A$6,0),LOOKUP(2.79,--(LEFT($B$1:$D$1,SEARCH(">",$B$1:$D$1)-1)),COLUMN($B$1:$D$1)-MIN(COLUMN($B$1:$D$1)-2)))

 

[overbomb]

Hello Maddy, I think you are looking for something like this, assumes your table in in range A1:D:6

=INDEX(A1:D6,MATCH("Employee2",$A$1:$A$6,0),LOOKUP(2.79,--(LEFT($B$1:$D$1,SEARCH(">",$B$1:$D$1)-1)),COLUMN($B$1:$D$1)-MIN(COLUMN($B$1:$D$1)-2)))

Hello and Thank you

has worked a treat

 

[Joyner]

You are welcome