IF function with multiple possibilities

**eighcoop** · 01-05-2010, 08:34 PM

Hey,

I want to create a rule or an IF function to give me a percentage based on a range of possible totals. Example:

If value is under 100, 15% will be charged.
If value is between 101-200, 10% will be charged.
If value is 201 or greater, 5% will be charged.

Say that my values are in column C and I want the corresponding percentage to be generated using a rule or IF function to be displayed in column A. Is this possible? If so, how would write the function?

(I only need the number to appear) ex: If my value is 120 in column C, I want the function to insert 10% in column A.

Sorry if this not worded or explained clearly, but thank you for the help.

**davesexcel** · 01-05-2010, 08:56 PM

This formula does what you require.

=IF(C1<=100,C1*0.15,IF(AND(C1>100,C1<=200),C1*0.1,IF(C1>200,C1*0.05,"")))

**teylyn** · 01-05-2010, 09:14 PM

Alternative approach

according to this

If my value is 120 in column C, I want the function to insert 10% in column A.

If you just want to show the percentage in column A, then use

=LOOKUP(C1,{0,100,200},{0.15,0.1,0.05})

format as %

Or calculate the 10% of the 120 in column C and show the result 12 without an IF statement

=LOOKUP(C1,{0,100,200},{0.15,0.1,0.05})*C1

format as number, decimals as required.

**eighcoop** · 01-06-2010, 02:26 AM

Thank you teylyn that does exactly what I needed!

Is it possible to set the function on an entire column? Referencing my first post: if I had for example 50 random totals in column C and I wanted column A to display the percentage; can I make the function give me a percentage for each value I have in Column C?

The first LOOKUP function is exactly what I want I just don't know how to make it so the function works for the entire column and I don't have to rewrite the function multiple times for each value.

Thanks again.

**teylyn** · 01-06-2010, 02:34 AM

Put the function into a cell in row 1 and then just copy it down. If your first row has headers, and you want the formula to start in row 2, adjust it to

=LOOKUP(C2,{0,100,200},{0.15,0.1,0.05})

before copying it down.

hth

**Rickastar** · 08-15-2015, 06:56 PM

Hey Guys,
I have an IF formula that is working, but I want to add to it
=IF(D6="I","",IF(B6>0,B6-K6,IF(B6<0,B6-L6)))
I would like to add that if C6="ST" then this column will remain blank as well, not sure why I can't get it to work.
I have tried
=IF(D6="I","",IF(B6>0,B6-K6,IF(B6<0,B6-L6,IF(C6="ST",""))))

**FDibbins** · 08-15-2015, 07:44 PM

Rickastar welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

(check to make sure that D6 really does contain I, and that there are no leading/trailing spaces)