Prev: Auto calculate
Next: How do I find a value in a table without using the labels?
From: Lars-�ke Aspelin on 18 Mar 2010 17:36
On Thu, 18 Mar 2010 14:59:03 +0000, Robb27
<Robb27.480yon(a)thecodecage.com> wrote:

>
>Lars-�ke Aspelin;674606 Wrote:
>>
>On Wed, 17 Mar 2010 14:45:03 -0700, Robb27
>> <Robb27(a)discussions.microsoft.com> wrote:
>>
>> >I have 3 cells in a row. We'll call them cell 1, 2 & 3. I need a
>formula in
>> >cell 3 that is based on what is in cells 1 and 2. Example:
>> >
>> >1 - If there is a letter "A" in cell 1 or 2, then concatenate cells 1
>and 2
>> >in cell 3.
>> >2 - If there is number in cell 1 and 2, then sum cells 1 and 2 in cell
>3.
>> >
>> >Thanks for your help.
>> >
>> >Rob
>>
>>
>> If cell 1 is cell A1 and cell 2 is cell A2, try this formula in cell
>> 3:
>>
>> =IF(OR(A1="A",A2="A"),A1&A2,IF(AND(ISNUMBER(A1),ISNUMBER(A2)),A1+A2,""))
>>
>> Hope this helps / Lars-�ke
>
>
>
>Hi Lars-Ake,
>
>Your formula in cell A3 worked great. Here is another question for you
>if you don't mind.
>
>Here is an example:
>Cell A1 Cell A2 Cell A3 Cell A4
>50 9 9 18 #N/A
>
>
>I have your formula in cell A3 - In cell A4 I have this formula:
>INDEX(Basic_Strategy_Chart!$B$2:$K$41,MATCH(AN9,Basic_Strategy_Chart!$A$2:$A$41,0),MATCH(D9,Basic_Strategy_Chart!$B$1:$K$1,0))
>
>My problem now is I can't figure out why my formula in Cell A4 returns
>an N/A result. This happens whenever I have 2 of the same number in A1
>and A2. When there are different numbers in A1 and A2, or if there is an
>"A" in one of the cells, my formula works great. Can you see a problem,
>or do I need to send you my sheet for your review? Let me know. and
>thanks for your help.
>
>Rob


You will get the #N/A error if either of the MATCH functions will not
find their respective lookup value in their respective lookup array.
So, what formulas do you have in the cells AN9 and D9?
Obviously something that, directly or indirectly, uses the values of
cells A1 or A2.

Lars-�ke
From: Robb27 on 19 Mar 2010 01:22

Lars-�ke Aspelin;675699 Wrote:
>
On Thu, 18 Mar 2010 14:59:03 +0000, Robb27
> <Robb27.480yon(a)thecodecage.com> wrote:
>
> >
> >Lars-�ke Aspelin;674606 Wrote:
> >>
> >On Wed, 17 Mar 2010 14:45:03 -0700, Robb27
> >> <Robb27(a)discussions.microsoft.com> wrote:
> >>
> >> >I have 3 cells in a row. We'll call them cell 1, 2 & 3. I need a
> >formula in
> >> >cell 3 that is based on what is in cells 1 and 2. Example:
> >> >
> >> >1 - If there is a letter "A" in cell 1 or 2, then concatenate cells
1
> >and 2
> >> >in cell 3.
> >> >2 - If there is number in cell 1 and 2, then sum cells 1 and 2 in
cell
> >3.
> >> >
> >> >Thanks for your help.
> >> >
> >> >Rob
> >>
> >>
> >> If cell 1 is cell A1 and cell 2 is cell A2, try this formula in
cell
> >> 3:
> >>
> >>
=IF(OR(A1="A",A2="A"),A1&A2,IF(AND(ISNUMBER(A1),ISNUMBER(A2)),A1+A2,""))
> >>
> >> Hope this helps / Lars-�ke
> >
> >
> >
> >Hi Lars-Ake,
> >
> >Your formula in cell A3 worked great. Here is another question for
you
> >if you don't mind.
> >
> >Here is an example:
> >Cell A1 Cell A2 Cell A3 Cell A4
> >50 9 9 18 #N/A
> >
> >
> >I have your formula in cell A3 - In cell A4 I have this formula:
> >INDEX(Basic_Strategy_Chart!$B$2:$K$41,MATCH(AN9,Basic_Strategy_Chart!$A$2:$A$41,0),MATCH(D9,Basic_Strategy_Chart!$B$1:$K$1,0))
> >
> >My problem now is I can't figure out why my formula in Cell A4
returns
> >an N/A result. This happens whenever I have 2 of the same number in
A1
> >and A2. When there are different numbers in A1 and A2, or if there is
an
> >"A" in one of the cells, my formula works great. Can you see a
problem,
> >or do I need to send you my sheet for your review? Let me know. and
> >thanks for your help.
> >
> >Rob
>
>
> You will get the #N/A error if either of the MATCH functions will not
> find their respective lookup value in their respective lookup array.
> So, what formulas do you have in the cells AN9 and D9?
> Obviously something that, directly or indirectly, uses the values of
> cells A1 or A2.
>
> Lars-�ke



Thanks Lars-Ake,

I figured out my problem with the lookup value. Thanks for pointing me
in the right direction. I have another issue I just discovered. The page
almost works perfect, but the problem is...what if both values in cell
A1 and A2 are the same? Can we tweek your formula for these parameters?
Example:

I have 3 cells in a row. We'll call them cell A1, B1 & C1. I need a
formula in cell C1 that is based on what is in cells A1 and B1.
Example:

1 - If there is a letter "A" in cell A1 or B1, then concatenate cells
A1 and B1 in cell C1.
2 - If there is number in cell 1 and 2, then sum cells A1 and B1 in
cell C1.
3 - If there are the same numbers in cell A1 and B1, then concatenate
cells A1 and B1 in cell C1.

You will note the additional parameter of the same number in both A1
and B1. That should do it. Again, thanks for looking at this for me.

Rob


--
Robb27
------------------------------------------------------------------------
Robb27's Profile: 1620
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=188456

http://www.thecodecage.com/forumz/chat.php

First  |  Prev  | 
Pages: 1 2
Prev: Auto calculate
Next: How do I find a value in a table without using the labels?