Formula based on a cell entry

  • Thread starter Thread starter Fat Bastard
  • Start date Start date
F

Fat Bastard

I am not knowledgable in Excel and I am trying to figure out how to do
something.

I want some cells in a row to choose between 2 or 3 formulas based on
the value I enter for a certain cell in the same row.

For example: If I enter "A" in F9 it would use Formula A in X9, AC9,
etc. If I enter "B" in F9 it would use Formula B in X9, AC9, etc.

How would I do this? I am not even sure of the terminology of the
solution to research it. Variable, IF, ???????

Thanks.
 
Here's one possibiliy. You could use a formula like this
one in each cell (X9, AC9, etc.):

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

In this case, if F9 = "A", then col. A is summed. If F9
= "B", sum col. B, and if F9 = "C", sum col. C.

HTH
Jason
Atlanta, GA
 
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
 
Thanks for the responses.

Here is a more specific example of what I am doing.

Here are the 2 formulas I have.

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

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

If I put A in F9 it will use the first one and B in F9 will use the
second one.


Here is what I made based on the suggestion below. Excel didn't want
to accept it.
Note: I use a word instead of A or B.


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

Note: in F9 I made a drop-down list so it can pick A or B. Would that
tamper with the formula?
 
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
 
Thanks for all your help on this. I couldn't figure it out so I slept
on it and now I have it.

Here is the formula that worked.

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

It changes throughout the sheet, but used this formula as the example
to just change numbers around.

The numbers used in A and B are the same except B goes up to 480.The
numbers go like this.

A B
15 15
30 30
60 60
120 120
240 240
480

I have it all figured out except for one part. Here are the 2 formulas
to combine like the previous one.

=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(AND(F9="B",G9<480),"",480)
 
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
 
Wouldn't it be along the same lines as the previous formula
constructed based on the value of F9?
=IF(G9<240,"",240)
=IF(G9<480,"",480)

If G9 < 240 it's also < 480, right?
Click to expand...

Those are the 2 formulas I combined to make this one based on F9:

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

It is based on the value of F9 just like the previous one I combined
that you helped me with. Is there a way to fix the formula above? Can
something like the formula above accomplish what I want?

B goes up to 480 and A only goes up to 240
 
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
 
Back
Top