Solved

# create a formula that allows three options (if 1 then 2 or if 3 then 4 or if 5 then 6)

Posted on 2009-02-18
234 Views
I am trying to create a formula that allows me to populate a cell with one of three options. Effectively I'm trying to multiply one cell (dollar amount of a particular sale) on the worksheet by .5 if a different cell (the total of all sales for the month) is less than \$2,000,000 or by .6 if between 2m and 2.5m or by .65 if over 2.5m.  A normal if formula doesn't give me enough options and I've never done any VB programing. Any ideas?
0
Question by:ISBTECH

LVL 59

Expert Comment

There you go you can use this if formula...which will do what you want...
lets say you have value in a1 then...
=if(a1=<2000000,a1*.5,if(and(a1>2000000,a1=<2500000),a1*.6,a1*65))
Saurabh...
0

LVL 50

Accepted Solution

You can do without the AND....
=if(a1<2000000,a1*0.5,if(a1=<2500000,a1*0.6,a1*0.65))
regards, barry
0

Author Comment

Thanks both of you that worked great!
0

LVL 59

Expert Comment

Any particular reason for not splitting the points equally..if both the suggestions work for you..??
0

LVL 59

Expert Comment

Sorry, I posted a little too quickly. I can see that Barry's formula is better than mine and that is why you selected it
0

Author Comment

Actualy it had not occurred to me that I could split the points, I would have done so if I had thought about it, I will keep it in mind from now on.  Thanks.
0

## Featured Post

User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …