# EXCEL: Conditional Formula with a range of variables

Discussion in 'Business Applications' started by Stuff4Toys, Dec 1, 2010.

Not open for further replies.
1. Columns = A=Cost B=MAP C=Code D=Price

I need to calculate the value of a formula with different values. Let me try and explain it like this.

In Column D
=IF(A1<=25,A1*1.4) ok, I get this part but how do I get the same calc for different values?
=IF(A1<=50,A1*1.3)
=IF(A1<=100,A1*1.2)
=IF(A1>100,A1*1.1)

How do I combine all these argumants into one formula? Could use a lookup table so I can change the values without re-doing all the formulas.

Then to confuse the whole matter I need to add the following condition with the HIGHEST priority so if this statement is True, then ignore the rest.
=IF(C1=1,B1)

I never was real good at creating Logic statements. I understand the concept, but putting it all together is another story.

JOhn ><>

2. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,371
First Name:
Wayne
you can nest them

so
IF( C1 = 1, B1 , IF(A1>100,A1*1.1, IF(A1<=25,A1*1.4, etc ))))))

Just a quick reply - need to look at little longer to see the best setup
but thats the theory

3. OK, I was getting waaay to many parens in all the wrong places. Thanks for the help. The next project is to get all the multipliers in a look table in an external spreadsheet. But that's the future.

JOhn ><>

4. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,371
First Name:
Wayne
OK - try this
=IF(C1=1, B1, IF(A1<=25, A1*1.4, IF( A1<=50, A1*1.3, IF(A1<=100, A1*1.2, A1*1.1))))

5. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,371
First Name:
Wayne
6. Don't know if it makes a difference, but it's Office 2007. Here's what I ended up with, along with some "autocorrect" from Excel. Obviously working with Row 9.

=IF(C9=1,B9,IF(A9<=25,A9*1.4,IF(A9<=50,A9*1.3,IF(A9<=75,A9*1.2,IF(A9<=100,A9*1.15,IF(A9>100,A9*1.1))))))

Next trick will be putting the multipliers in an external table, but that should be pretty easy. I can create another Spreadsheet and save it in the same folder. Then copy & paste, should bring the filename and cell location from the new sheet.

JOhn ><>

7. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,371
First Name:
Wayne
did that work ?
IF(A9<=100,A 9*1.15,IF(A9>100,A9*1.1

you dont need the last IF
because you finished on a9<=100
then you have covered all the test
therefore the number must be above 100
so instead of another IF statement you can just put in the * factor

IF(A9<=100,A 9*1.15, A9*1.1

Actually it will never be false so thats probably why its OK

also you have
IF(A9<=100,A 9*1.15
a space between A and 9
but that maybe a typo

you sneaked another criteria in 75  I'm not sure on excel 2007 how many nested IF you can have - i will need to check

8.     9. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,371
First Name:
Wayne
10. looks like it got caught in a line break, the space is not there.

11. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
having multiple spreadsheets to open - as opposed to having several worksheets in the same spreadsheet - can be cumbersome, for if all the sheets aren't open the data cannot be looked up and you will get errors.

12. In this case I have to do a daily download of a 6500 line text file and make manipulations, export to Tab delineated text and upload to a mySQL database. I will probably end up having a macro (???) created to make these and many other changes to speed the process up.

Need to do this function, delete several un-needed columns, combine the contents of two cells to make one, strip a URL and replace it with a relative path and so on and so forth, this is just the beginning.

I have used an external spreadsheet in the past, and did not have to have the sheet open to make it work, the formula had the path in it.
=[PriceData.xlsx]Sheet1!\$A\$9

JOhn ><>

13. ### slurpee55

Joined:
Oct 20, 2004
Messages:
7,837
The formula "=[PriceData.xlsx]Sheet1!\$A\$9" calls the worksheet [PriceData.xlsx] - it must have been open, although perhaps it ran in the background.
Given what you describe doing, I would suggest you really should get a hand from one (or several) of the VBA coders here (there are some great ones who pop in - alas, I'm not one of them.) Probably most of the job could be run automatically.