Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Fat Bastard
 
Posts: n/a
Default Formula based on a cell entry

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.
  #2   Report Post  
Jason Morin
 
Posts: n/a
Default

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

-----Original Message-----
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.
.

  #3   Report Post  
Biff
 
Posts: n/a
Default

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

-----Original Message-----
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.
.

  #4   Report Post  
Fat Bastard
 
Posts: n/a
Default

Thanks for the responses.

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

Here are the 2 formulas I have.

=IF(AND(G9119,G9<241),240,"")

=IF(AND(G9239,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(G9239,G9<481),480,""),IF(F9="A",( AND(G9119,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?




On Tue, 8 Mar 2005 11:04:08 -0800, "Biff"
wrote:

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

-----Original Message-----
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.
.


  #5   Report Post  
Biff
 
Posts: n/a
Default

Hi!

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


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(G9119,G9<241),240,IF(AND(G9239,G9<481),4 80,""))

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",G9119,G9<241),240,IF(AND
(F9="B",G9239,G9<481),480,""))

Using this formula, if F9=A and G9241 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(G9119,G9<241),240,"")

B =IF(AND(G9239,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

-----Original Message-----
Thanks for the responses.

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

Here are the 2 formulas I have.

=IF(AND(G9119,G9<241),240,"")

=IF(AND(G9239,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(G9239,G9<481),480,""),IF(F9="A", (AND

(G9119,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?




On Tue, 8 Mar 2005 11:04:08 -0800, "Biff"


wrote:

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),I F
(F9="C",MIN(A1:A5),"")))

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

Experiment!

Biff

-----Original Message-----
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.
.


.



  #6   Report Post  
Fat Bastard
 
Posts: n/a
Default

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",G9119,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<48 0),"",480)




On Tue, 8 Mar 2005 21:45:37 -0800, "Biff"
wrote:

Hi!

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


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(G9119,G9<241),240,IF(AND(G9239,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",G9119,G9<241),240,IF(AND
(F9="B",G9239,G9<481),480,""))

Using this formula, if F9=A and G9241 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(G9119,G9<241),240,"")

B =IF(AND(G9239,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

-----Original Message-----
Thanks for the responses.

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

Here are the 2 formulas I have.

=IF(AND(G9119,G9<241),240,"")

=IF(AND(G9239,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(G9239,G9<481),480,""),IF(F9="A" ,(AND

(G9119,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?




On Tue, 8 Mar 2005 11:04:08 -0800, "Biff"


wrote:

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

-----Original Message-----
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.
.


.


  #7   Report Post  
Biff
 
Posts: n/a
Default

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

-----Original Message-----
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",G9119,G9<241),240,IF(AND

(F9="B",G9239,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)




On Tue, 8 Mar 2005 21:45:37 -0800, "Biff"


wrote:

Hi!

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


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(G9119,G9<241),240,IF(AND(G9239,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",G9119,G9<241),240,IF(AND
(F9="B",G9239,G9<481),480,""))

Using this formula, if F9=A and G9241 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(G9119,G9<241),240,"")

B =IF(AND(G9239,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

-----Original Message-----
Thanks for the responses.

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

Here are the 2 formulas I have.

=IF(AND(G9119,G9<241),240,"")

=IF(AND(G9239,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(G9239,G9<481),480,""),IF(F9="A ",(AND

(G9119,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?




On Tue, 8 Mar 2005 11:04:08 -0800, "Biff"


wrote:

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

-----Original Message-----
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.
.


.


.

  #8   Report Post  
Fat Bastard
 
Posts: n/a
Default

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?


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<48 0),"",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?

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


B goes up to 480 and A only goes up to 240


On Fri, 11 Mar 2005 00:32:26 -0800, "Biff"
wrote:

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<48 0),"",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

-----Original Message-----
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",G9119,G9<241),240,IF(AND

(F9="B",G9239,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)




On Tue, 8 Mar 2005 21:45:37 -0800, "Biff"


wrote:

Hi!

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

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(G9119,G9<241),240,IF(AND(G9239,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",G9119,G9<241),240,IF(AND
(F9="B",G9239,G9<481),480,""))

Using this formula, if F9=A and G9241 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(G9119,G9<241),240,"")

B =IF(AND(G9239,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

-----Original Message-----
Thanks for the responses.

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

Here are the 2 formulas I have.

=IF(AND(G9119,G9<241),240,"")

=IF(AND(G9239,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(G9239,G9<481),480,""),IF(F9=" A",(AND
(G9119,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?




On Tue, 8 Mar 2005 11:04:08 -0800, "Biff"

wrote:

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

-----Original Message-----
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.
.


.


.


  #9   Report Post  
Biff
 
Posts: n/a
Default

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),4 80,""))

Biff

-----Original Message-----
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?


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?

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


B goes up to 480 and A only goes up to 240


On Fri, 11 Mar 2005 00:32:26 -0800, "Biff"


wrote:

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

-----Original Message-----
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",G9119,G9<241),240,IF(AND

(F9="B",G9239,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)




On Tue, 8 Mar 2005 21:45:37 -0800, "Biff"


wrote:

Hi!

Note: in F9 I made a drop-down list so it can pick A

or
B. Would that tamper with the formula?

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(G9119,G9<241),240,IF(AND

(G9239,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",G9119,G9<241),240,IF(AND
(F9="B",G9239,G9<481),480,""))

Using this formula, if F9=A and G9241 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(G9119,G9<241),240,"")

B =IF(AND(G9239,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

-----Original Message-----
Thanks for the responses.

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

Here are the 2 formulas I have.

=IF(AND(G9119,G9<241),240,"")

=IF(AND(G9239,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(G9239,G9<481),480,""),IF(F9= "A",(AND
(G9119,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?




On Tue, 8 Mar 2005 11:04:08 -0800, "Biff"

wrote:

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:A 5),IF
(F9="C",MIN(A1:A5),"")))

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

Experiment!

Biff

-----Original Message-----
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.
.


.


.


.

  #10   Report Post  
Fat Bastard
 
Posts: n/a
Default

That worked perfect. Thanks for helping me out through the whole
process.


On Sat, 12 Mar 2005 10:18:05 -0800, "Biff"
wrote:

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

-----Original Message-----
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?


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?

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


B goes up to 480 and A only goes up to 240


On Fri, 11 Mar 2005 00:32:26 -0800, "Biff"


wrote:

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

-----Original Message-----
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",G9119,G9<241),240,IF(AND
(F9="B",G9239,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)




On Tue, 8 Mar 2005 21:45:37 -0800, "Biff"

wrote:

Hi!

Note: in F9 I made a drop-down list so it can pick A

or
B. Would that tamper with the formula?

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(G9119,G9<241),240,IF(AND

(G9239,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",G9119,G9<241),240,IF(AND
(F9="B",G9239,G9<481),480,""))

Using this formula, if F9=A and G9241 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(G9119,G9<241),240,"")

B =IF(AND(G9239,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

-----Original Message-----
Thanks for the responses.

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

Here are the 2 formulas I have.

=IF(AND(G9119,G9<241),240,"")

=IF(AND(G9239,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(G9239,G9<481),480,""),IF(F9 ="A",(AND
(G9119,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?




On Tue, 8 Mar 2005 11:04:08 -0800, "Biff"

wrote:

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

-----Original Message-----
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.
.


.


.


.




  #11   Report Post  
Biff
 
Posts: n/a
Default

You're welcome! Thanks for the feedback.

Biff

-----Original Message-----
That worked perfect. Thanks for helping me out through

the whole
process.


On Sat, 12 Mar 2005 10:18:05 -0800, "Biff"


wrote:

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

-----Original Message-----
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?

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?

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

B goes up to 480 and A only goes up to 240


On Fri, 11 Mar 2005 00:32:26 -0800, "Biff"


wrote:

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

-----Original Message-----
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",G9119,G9<241),240,IF(AND
(F9="B",G9239,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)




On Tue, 8 Mar 2005 21:45:37 -0800, "Biff"

wrote:

Hi!

Note: in F9 I made a drop-down list so it can pick

A
or
B. Would that tamper with the formula?

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(G9119,G9<241),240,IF(AND

(G9239,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",G9119,G9<241),240,IF(AND
(F9="B",G9239,G9<481),480,""))

Using this formula, if F9=A and G9241 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(G9119,G9<241),240,"")

B =IF(AND(G9239,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

-----Original Message-----
Thanks for the responses.

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

Here are the 2 formulas I have.

=IF(AND(G9119,G9<241),240,"")

=IF(AND(G9239,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(G9239,G9<481),480,""),IF(F 9="A",

(AND
(G9119,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?




On Tue, 8 Mar 2005 11:04:08 -0800, "Biff"

wrote:

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

-----Original Message-----
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.
.


.


.


.


.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula that referance a Cell MESTRELLA29 Excel Discussion (Misc queries) 1 February 11th 05 12:18 AM
Can a Formula in Cell X modify Cell Y? alMandragor Excel Discussion (Misc queries) 7 February 10th 05 10:51 PM
Cell contents vs. Formula contents Sarah Excel Discussion (Misc queries) 3 December 15th 04 07:02 PM
How do I do math on a cell name in formula? Mark Mulik Excel Worksheet Functions 3 November 23rd 04 04:43 PM
Cell doesn't show formula result - it shows formula (CTRL + ' doe. o0o0o0o Excel Worksheet Functions 6 November 19th 04 04:13 PM


All times are GMT +1. The time now is 04:24 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"