Formula based on a cell entry

B

Biff

Hi!

Try something like this as long as you only have 2 or 3
conditions:

=IF(F9="A",SUM(A1:A5),IF(F9="B",AVERAGE(A1:A5),IF
(F9="C",MIN(A1:A5),"")))

A = sum the range
B = average the range
C = minimum value in the range

Experiment!

Biff
 
B

Biff

Hi!
Note: in F9 I made a drop-down list so it can pick A or
B. Would that tamper with the formula?
Click to expand...

No, that'll have no negative impact. The problem is that
the formula is not properly constructed.

I can't really see why you need to use A= this formula and
B= that formula unless A and B are related to specific and
separate criteria.

If you simply want to return different values based on the
value of G9, you could do this:

=IF(AND(G9>119,G9<241),240,IF(AND(G9>239,G9<481),480,""))

But, if you do need to base things on F9 equalling either
A or B here are a couple of ideas:

=IF(AND(F9="A",G9>119,G9<241),240,IF(AND
(F9="B",G9>239,G9<481),480,""))

Using this formula, if F9=A and G9>241 you'll get "". Not
sure that's what you want but that's how you're separate
formulas would have handled it.

Here's something that uses Jason's approach with a little
twist.

Create defined names for the 2 formulas:

A =IF(AND(G9>119,G9<241),240,"")

B =IF(AND(G9>239,G9<481),480,"")

Then use this formula:

=CHOOSE(MATCH(F9,{"A","B"},0),A,B)

This will work the same as the second formula I show above.

Biff
 
B

Biff

Hi!

.....

=IF(G9<240,"",240)
=IF(G9<480,"",480)

Here is what I came up with. Excel accepted the entry, but
gives me #VALUE! error.

=IF(AND(F9="A",G9<240),"",240),IF(AN(F9="B",G9<480),"",480)

.....

The formula is not properly constructed but that doesn't
matter because you have a logic mis-match:

=IF(G9<240,"",240)
=IF(G9<480,"",480)

If G9 < 240 it's also < 480, right?

Can't figure out what you mean with this one but the
problem is that you have 2 different return values for
only one condition.

Biff
 
B

Biff

Hi!

Let me see if I understand this:

If F9 = A, G9 will not be >240

and

If F9 = B, G9 will not be >480

If that's true, try this:

=IF(AND(F9="A",G9=240),240,IF(AND(F9="B",G9=480),480,""))

Biff
 
Top