1. Welcome to PlowSite. Notice a fresh look and new features? It’s now easier to share photos and videos, find popular topics fast, and enjoy expanded user profiles. If you have any questions, click HELP at the top or bottom of any page, or send an email to help@plowsite.com. We welcome your feedback.

    Dismiss Notice

Excel formulas

Discussion in 'Business Fundamentals' started by Lawn Lad, Aug 26, 2003.

  1. Lawn Lad

    Lawn Lad Senior Member
    Messages: 407

    I'm working on a spread sheet to manage snow plow contracts and the pricing. For seasonal contracts which may be one or two inch triggers, I'm trying to automate the pricing step. I'm trying to use an if/then statement, but I need two scnarios to be evaluated, so I thought I'd use the "OR" logic. But it won't work.

    =IF(AG8="one",(AP8*AJ2),0)*OR(AG8="two",(AP8*AJ3),0)

    If the column AG8 reads "one" than I want to take the unit price and multiple it by the factor for 1" trigger accounts in AJ2, or if AG8 reads "two" than I want the unit price to be mutiplied times the factor for 2" trigger accounts in AJ3.

    The logic works for the 1" trigger calculation, but when I change the definition to "two" for the 2" trigger, I'm left with nothing in the cell.

    What does the "*" symbol do in the equation next to the "OR" statement?
     
  2. HenkeRep

    HenkeRep Junior Member
    Messages: 10

    I'm pretty good with excel...why don't you e-mail me the spreadsheet, and let me screw around with it.

    Maybe I can help?
     
  3. BRL

    BRL PlowSite.com - Veteran
    Messages: 1,277

    The asterick multiplies the stuff before it to the stuff after it. So that probably shouldn't be there? Like Herkerep, if I am working with the file I can figure it out, but not reading your post LOL.
     
  4. Lawn Lad

    Lawn Lad Senior Member
    Messages: 407

    Solved it earlier today.

    =IF($AJ35="NA","NA",(IF($AK35="one",$AE35*AN$2,$AE35*AN$3)))

    The NA, meaning Not Available, is printed in the pricing cell only if that service is not available. Otherwise, by using "one" as the only criteria, anything else is teh default. So this only allows two variables. This formula can be expanded to include more variables, you just keep adding onto it with IF( statements. Excel is cool.

    Thanks for the help guys.