Hello - I am trying to add a function to a spreadsheet that means when 2
variables are completed (currency & month - 4 options on each) it returns a
predetermined value. I thought I'd nearly got there (with a large amount of
help) by using a combination of If & AND functions, but it keeps giving an
error message. Can anyone help?

In case, I'm not being clear, I need a formula that will deal with this:
If M =1, C=1, then E = a
If M =1, C=2, then E = b
If M =1, C=3, then E = c
If M =1, C=4, then E = d
If M =2, C=1, then E = e
If M =2, C=2, then E = f
If M =2, C=3, then E = g
If M =2, C=4, then E = h
If M =3, C=1, then E = i
If M =3, C=2, then E = j
If M =3, C=3, then E = k
If M =3, C=4, then E = l
If M =4, C=1, then E = m
If M =4, C=2, then E = n
If M =4, C=3, then E = o
If M =4, C=4, then E = p

Where M and C are selected by the user from 4 options, and E is the result
given from values that I have provided. Any ideas?
Many thanks
N

Hi,

Set up a table like this

1 2 3 4
1 a e i m
2 b f j n
3 c g k o
4 d h l p

then us a formula like this
=INDEX($B$2:$E$5,MATCH(A11,$A$2:$A$5,),MATCH(A12,$ B$1:$E$1,))

In this case the user is picking the M value from A11 and C value from A12
and the above table is in A1:E5

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Ned Harrison" wrote:

Hello - I am trying to add a function to a spreadsheet that means when 2
variables are completed (currency & month - 4 options on each) it returns a
predetermined value. I thought I'd nearly got there (with a large amount of
help) by using a combination of If & AND functions, but it keeps giving an
error message. Can anyone help?

In case, I'm not being clear, I need a formula that will deal with this:
If M =1, C=1, then E = a
If M =1, C=2, then E = b
If M =1, C=3, then E = c
If M =1, C=4, then E = d
If M =2, C=1, then E = e
If M =2, C=2, then E = f
If M =2, C=3, then E = g
If M =2, C=4, then E = h
If M =3, C=1, then E = i
If M =3, C=2, then E = j
If M =3, C=3, then E = k
If M =3, C=4, then E = l
If M =4, C=1, then E = m
If M =4, C=2, then E = n
If M =4, C=3, then E = o
If M =4, C=4, then E = p

Where M and C are selected by the user from 4 options, and E is the result
given from values that I have provided. Any ideas?
Many thanks
N

So, are you saying that in your example "a", "b", "c" etc represent
values that you want to return rather than those letters literally?

One way of doing it is to put those values in a column somewhere, eg
in X1:X16, in the order shown, then you could use this in the cell you
refer to as E:

=INDEX(X$1:X$16,(M-1)*4+C)

where M and C will have to be changed to the cell references that they
represent.

You might like to build in some error-checking:

=IF(OR(M="",C=""),"",INDEX(X$1:X$16,(M-1)*4+C))

Hope this helps.

Pete


On Aug 28, 6:34*pm, Ned Harrison <Ned
wrote:
Hello - I am trying to add a function to a spreadsheet that means when 2
variables are completed (currency & month - 4 options on each) it returns a
predetermined value. *I thought I'd nearly got there (with a large amount of
help) by using a combination of If & AND functions, but it keeps giving an
error message. *Can anyone help?

In case, I'm not being clear, I need a formula that will deal with this:
If M =1, C=1, then E = a
If M =1, C=2, then E = b
If M =1, C=3, then E = c
If M =1, C=4, then E = d
If M =2, C=1, then E = e
If M =2, C=2, then E = f
If M =2, C=3, then E = g
If M =2, C=4, then E = h
If M =3, C=1, then E = i
If M =3, C=2, then E = j
If M =3, C=3, then E = k
If M =3, C=4, then E = l
If M =4, C=1, then E = m
If M =4, C=2, then E = n
If M =4, C=3, then E = o
If M =4, C=4, then E = p

Where M and C are selected by the user from 4 options, and E is the result
given from values that I have provided. *Any ideas?
Many thanks
N

That is absolutely brilliant - Thank you!

"Shane Devenshire" wrote:

Hi,

Set up a table like this

1 2 3 4
1 a e i m
2 b f j n
3 c g k o
4 d h l p

then us a formula like this
=INDEX($B$2:$E$5,MATCH(A11,$A$2:$A$5,),MATCH(A12,$ B$1:$E$1,))

In this case the user is picking the M value from A11 and C value from A12
and the above table is in A1:E5

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Ned Harrison" wrote:

Hello - I am trying to add a function to a spreadsheet that means when 2
variables are completed (currency & month - 4 options on each) it returns a
predetermined value. I thought I'd nearly got there (with a large amount of
help) by using a combination of If & AND functions, but it keeps giving an
error message. Can anyone help?

In case, I'm not being clear, I need a formula that will deal with this:
If M =1, C=1, then E = a
If M =1, C=2, then E = b
If M =1, C=3, then E = c
If M =1, C=4, then E = d
If M =2, C=1, then E = e
If M =2, C=2, then E = f
If M =2, C=3, then E = g
If M =2, C=4, then E = h
If M =3, C=1, then E = i
If M =3, C=2, then E = j
If M =3, C=3, then E = k
If M =3, C=4, then E = l
If M =4, C=1, then E = m
If M =4, C=2, then E = n
If M =4, C=3, then E = o
If M =4, C=4, then E = p

Where M and C are selected by the user from 4 options, and E is the result
given from values that I have provided. Any ideas?
Many thanks
N

Hi Ned,

The usual way will require a lot of if's and goes perhaps beyond the limits
of Excel (I didn't try)...

I tried following and was working for me (if did understand your question
correctly...)

In column A, I've put M (1,1, 1, 1, 2, 2, 2, ...)
In column B, I've put C (1, 2, 3, 4, 1, 2, 3? 4 ...

In column C, (E result) following formula:

=IF(A1=1;CHAR(96+B1);IF(A1=2;CHAR(100+B1);IF(A1=3; CHAR(104+B1);CHAR(108+B1))))

The CHAR function gives the corresponding character of the ASCII value: 97
= a, 98 = b, ....

Note that you may have to change ';' in ',' to get the formula working.

Please let me know if this was helpful.

Wkr,

JP


"Ned Harrison" <Ned wrote in message
...
Hello - I am trying to add a function to a spreadsheet that means when 2
variables are completed (currency & month - 4 options on each) it returns
a
predetermined value. I thought I'd nearly got there (with a large amount
of
help) by using a combination of If & AND functions, but it keeps giving an
error message. Can anyone help?

In case, I'm not being clear, I need a formula that will deal with this:
If M =1, C=1, then E = a
If M =1, C=2, then E = b
If M =1, C=3, then E = c
If M =1, C=4, then E = d
If M =2, C=1, then E = e
If M =2, C=2, then E = f
If M =2, C=3, then E = g
If M =2, C=4, then E = h
If M =3, C=1, then E = i
If M =3, C=2, then E = j
If M =3, C=3, then E = k
If M =3, C=4, then E = l
If M =4, C=1, then E = m
If M =4, C=2, then E = n
If M =4, C=3, then E = o
If M =4, C=4, then E = p

Where M and C are selected by the user from 4 options, and E is the result
given from values that I have provided. Any ideas?
Many thanks
N

Thanks for the feedback.


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Shane Devenshire" wrote:

Hi,

Set up a table like this

1 2 3 4
1 a e i m
2 b f j n
3 c g k o
4 d h l p

then us a formula like this
=INDEX($B$2:$E$5,MATCH(A11,$A$2:$A$5,),MATCH(A12,$ B$1:$E$1,))

In this case the user is picking the M value from A11 and C value from A12
and the above table is in A1:E5

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Ned Harrison" wrote:

Hello - I am trying to add a function to a spreadsheet that means when 2
variables are completed (currency & month - 4 options on each) it returns a
predetermined value. I thought I'd nearly got there (with a large amount of
help) by using a combination of If & AND functions, but it keeps giving an
error message. Can anyone help?

In case, I'm not being clear, I need a formula that will deal with this:
If M =1, C=1, then E = a
If M =1, C=2, then E = b
If M =1, C=3, then E = c
If M =1, C=4, then E = d
If M =2, C=1, then E = e
If M =2, C=2, then E = f
If M =2, C=3, then E = g
If M =2, C=4, then E = h
If M =3, C=1, then E = i
If M =3, C=2, then E = j
If M =3, C=3, then E = k
If M =3, C=4, then E = l
If M =4, C=1, then E = m
If M =4, C=2, then E = n
If M =4, C=3, then E = o
If M =4, C=4, then E = p

Where M and C are selected by the user from 4 options, and E is the result
given from values that I have provided. Any ideas?
Many thanks
N

Try this:

=CHAR(4*M1+C1+92)



"Ned Harrison" wrote:

Hello - I am trying to add a function to a spreadsheet that means when 2
variables are completed (currency & month - 4 options on each) it returns a
predetermined value. I thought I'd nearly got there (with a large amount of
help) by using a combination of If & AND functions, but it keeps giving an
error message. Can anyone help?

In case, I'm not being clear, I need a formula that will deal with this:
If M =1, C=1, then E = a
If M =1, C=2, then E = b
If M =1, C=3, then E = c
If M =1, C=4, then E = d
If M =2, C=1, then E = e
If M =2, C=2, then E = f
If M =2, C=3, then E = g
If M =2, C=4, then E = h
If M =3, C=1, then E = i
If M =3, C=2, then E = j
If M =3, C=3, then E = k
If M =3, C=4, then E = l
If M =4, C=1, then E = m
If M =4, C=2, then E = n
If M =4, C=3, then E = o
If M =4, C=4, then E = p

Where M and C are selected by the user from 4 options, and E is the result
given from values that I have provided. Any ideas?
Many thanks
N

Hi Teetless mama,

That's what I would have answered if I was able to set-up the formula. Was
busy with M1^2 and didn't see 4*M1.

Wkr,

JP


"Teethless mama" wrote in message
...
Try this:

=CHAR(4*M1+C1+92)



"Ned Harrison" wrote:

Hello - I am trying to add a function to a spreadsheet that means when 2
variables are completed (currency & month - 4 options on each) it returns
a
predetermined value. I thought I'd nearly got there (with a large amount
of
help) by using a combination of If & AND functions, but it keeps giving
an
error message. Can anyone help?

In case, I'm not being clear, I need a formula that will deal with this:
If M =1, C=1, then E = a
If M =1, C=2, then E = b
If M =1, C=3, then E = c
If M =1, C=4, then E = d
If M =2, C=1, then E = e
If M =2, C=2, then E = f
If M =2, C=3, then E = g
If M =2, C=4, then E = h
If M =3, C=1, then E = i
If M =3, C=2, then E = j
If M =3, C=3, then E = k
If M =3, C=4, then E = l
If M =4, C=1, then E = m
If M =4, C=2, then E = n
If M =4, C=3, then E = o
If M =4, C=4, then E = p

Where M and C are selected by the user from 4 options, and E is the
result
given from values that I have provided. Any ideas?
Many thanks
N

Thanks people for all the responses. I've used Shane's version which seems
to work fine for what I need - but I really appreciate all the help. All of
your solutions look a lot simpler than my starting point. Cheers!