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.

Conditional Value Excel Formula

Discussion in 'Business Applications' started by cmcnally, Aug 3, 2005.

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

    cmcnally Thread Starter

    Joined:
    Jan 23, 2002
    Messages:
    300
    I'm trying to improve my skill with Excel and I'm having some trouble with a difficult formula. Basically, it involves two conditions. I think it will be easier if I just write out the logic formulas I'm trying to enter. G12 and F12 are the two cells that determine the value of H12, the cell I'm having trouble with.

    If G12<3 and F12<50=.2
    If G12<3 and 51<F12<100=.24
    If G12<3 and F12>100=.26

    If G12>2 and F12<100=.2
    If G12>2 and 101<F12<200=.24
    If G12>2 and F12>200=.26

    So I need a way of expressing all six of those conditions in one formula. This is a variable rate for a timesheet and as you can see, the rate changes depending on whether there are more than 2 people on staff (the G12 value.) I know it would be relatively simple to just enter the rates manually, but I've been wanting to learn more about how Excel works for some time, and just trying to work this out has already taught me a lot so far. At this point, however, I'm stuck.
     
  2. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    If G12 is a # of people thing, then on one hand you have a set of circumstances for "# of people is less than 3", while on the other hand you have a set of circumstances for "# of people is greater than 2". Since you can't have less than whole people these would seem to conflict.

    Any chance you could explain it differently?
     
  3. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    I assume you mean 3 options for F12:

    (i) less than 50

    (ii) greater than 50, less than 100

    (iii) greater than 100.

    You'll also need to cater for =50 or =100, by using <= (less than or equal to) and/or >= (greater than or equal to).
     
  4. cmcnally

    cmcnally Thread Starter

    Joined:
    Jan 23, 2002
    Messages:
    300
    Lost one of my replies, but the equal to thing gives me what I needed to express the first part of the formula clearly. Yes G12 is a # of people, and if the # is <=2, then the first rate structure applies, while if the # is >2, then the second rate structure would be used instead.
     
  5. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    OK, here's the basic structure:

    =IF(G12<=2,"X","Y") is the primary ("overall") formula.

    From there you need to define the "sub"-formulas to replace "X" & "Y".

    Back in a bit.
     
  6. XL Guru

    XL Guru

    Joined:
    Aug 30, 2003
    Messages:
    2,702
    So it'd be something like:

    =IF(G12<=2,
    IF(F12<=50,0.2,IF(F12<=100,0.24,0.26)),
    IF(F12<=100,0.2,IF(F12<=200,0.24,0.26)))

    The red bit kicks in if G12 is 2 or less. The blue bit kicks in if G12 is greater than 2.

    NB: you can split a formula into sections within the formula bar using ALT+Enter, sometimes this makes it easier to see what you're doing. The main thing is you need to be clear on your threshholds (i.e. whether something should apply if a value is less than 50 or less than or equal to 50).

    HTH,
    Andy
     
  7. cmcnally

    cmcnally Thread Starter

    Joined:
    Jan 23, 2002
    Messages:
    300
    Thanks so much for your help. I see now, how the formula is actually structured and what all the arguments do in sequence. Thanks again!
     
  8. 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/387057

  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