Formula to return specified value based on criteria

[adriam25]

I want to do this: If b2 is between 100% and 105% then show on C2 100%

for me that's like impossible. thanks./

[FDibbins]

Hi, welcome to the forum

there are a few different ways of doing this (and you did not specify what you want if B2 is outside those boundries?)

Options 1
=min(1,B2)

Option 2
=IF(AND(B2>=1,B2<=1.05),1,"")

Moderators note: I changed your thread title for you, please take a moment to read our forum rules, in this case, rule 1

[adriam25]

ok. thanks for replying. i didn't know about the rule. but thanks now i know. i think i'm not explaining what i want to do. let see. Ok if i get to the 23.75% of my goal i will meet the 100% of my commission. but also i can exceed the or decrease depending for ex: i sold $1000 this month and thats my commission if i get the 23.75% of the close rate. but if i just got the 18% of my CR then i will get just the 75.8% out of those $1000 . i don't know if you guys understand now. sorry for my English.

[FDibbins]

OK thats a bit different from your initial question

Sounds like you have some sort of "table of rates" for your commission? If so, then perhaps a vlookup will serve you better?

[adriam25]

hahahahaha yes we have tables of rates. here is an Screenshot of what i have so far. PAYOUT FORMULA.jpg

[FDibbins]

Its easier if you upload a workbook, not a pic, we cannot edit/change pics.

Having said that, change your Target column values to read lowest to highest, and then change them to only show the upper limit of each range...

	A	B	C	D
2	0%	-50%	106%	10%
3	75%	-30%
4	85%	-20%
5	95%	0%
6	105%	10%

C2=value to seach on
D2=VLOOKUP(C2,A2:B6,2,1)

[adriam25]

here is the workbook. and once again thanks for the help.

[FDibbins]

Im not going to reconstruct your table for you, you need to do some of the work yourself I have shown you the basis of what your table needs to look like, see if you can take it from there?

[Vikas_Gautam]

I have got the solution ( I hope..)
I have reorganized your table...

Please Login or Register  to view this content.

Check the attached file..