Carol Fraser

Board Regular
Here are my arguments:

If E3=85,.5
If E3=90,.75
If E3=95,.9
If E3>1,E3
If E3<.85,0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Jon von der Heyden

MrExcel MVP, Moderator
Perhaps:

=IF(ISNA(VLOOKUP(E3,{85,0.5;90,0.75;95,0.9},2,0)),IF(E3>1,E3,0),VLOOKUP(E3,{85,0.5;90,0.75;95,0.9},2,0))

...but what do you want to do if E3 is greater than 0.85 and less than 0?

EDIT: Hello Andrew... Long time

Last edited:

Andrew Fergus

MrExcel MVP
Hi Carol

Assuming you have made some typos with your example, for instance 85 should be .85 or 85%, what result would you want to see for 0.86?

Andrew

Carol Fraser

Board Regular
I'm a dork. Yes, there needs to be something in between. So, .5 for 85 to 90, .75 for 90 to 95, etc.

Thanks.

carol

Last edited:

Andrew Fergus

MrExcel MVP
Hi

Using Jon's approach, try this formula:
=IF(E3>=1,E3,VLOOKUP(E3,{0,0;0.85,0.5;0.9,0.75;0.95,0.9},2))

Andrew

P.S. Hi Jon, yes it has! I've been flat out lately......

Carol Fraser

Board Regular
Andrew, sorry but as I said earlier, I'm a dork. How would I write it if:

E3 is between .85 and .90, .5
E3 is between .90 and .95, .75
E3 is between .95 and 1.0, .9
E3 > 1, E3
E3 < .85, 0

schielrn

Well-known Member
His solution should meet your needs? Have you tried it, are you not getting the results you expect?

Andrew Fergus

MrExcel MVP
Hi Carol

As suggested by schielrn in what way is this not working? Are the values at each cusp causing problems - e.g. should 0.9 be 0.50 or 0.75?

And don't be too hard on yourself.....

Andrew

Replies
0
Views
150
Replies
1
Views
373
Replies
8
Views
227
Replies
13
Views
500
Replies
3
Views
246

1,195,673
Messages
6,011,087
Members
441,582
Latest member
Topkapi

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.

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

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