1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

EXCEL: Conditional Formula with a range of variables

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

Thread Status:
Not open for further replies.
Advertisement
  1. Stuff4Toys

    Stuff4Toys Thread Starter

    Joined:
    Dec 1, 2010
    Messages:
    6
    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. etaf

    etaf Moderator

    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. Stuff4Toys

    Stuff4Toys Thread Starter

    Joined:
    Dec 1, 2010
    Messages:
    6
    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. etaf

    etaf Moderator

    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. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,371
    First Name:
    Wayne
  6. Stuff4Toys

    Stuff4Toys Thread Starter

    Joined:
    Dec 1, 2010
    Messages:
    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.

    THANKS for your help.
    JOhn ><>
     
  7. etaf

    etaf Moderator

    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. Stuff4Toys

    Stuff4Toys Thread Starter

    Joined:
    Dec 1, 2010
    Messages:
    6
    (y)(y)(y)(y)(y)
    THANKS for your Help!
     
  9. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    65,371
    First Name:
    Wayne
  10. Stuff4Toys

    Stuff4Toys Thread Starter

    Joined:
    Dec 1, 2010
    Messages:
    6
    looks like it got caught in a line break, the space is not there.
     
  11. slurpee55

    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. Stuff4Toys

    Stuff4Toys Thread Starter

    Joined:
    Dec 1, 2010
    Messages:
    6
    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

    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.
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/965905

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice