# Conditional Value Excel Formula

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

Not open for further replies.

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

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

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

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

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

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

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!

As Seen On