G
Guest
"I want to insert one of three rates into a cell depending on which one of three alpha characters appears in another cell and which of two alpha characters appears in a third cell"
C
CLR
You can concatenate the two cells being tested and then create a VLOOKUP
table based on those concatenate combinations to return your results........
Vaya con Dios,
Chuck, CABGx3
characters appears in a third cell"
table based on those concatenate combinations to return your results........
Vaya con Dios,
Chuck, CABGx3
three alpha characters appears in another cell and which of two alphaWayne said:
characters appears in a third cell"
N
Norman Harker
Hi Wayne!
I assume that there are in fact six rates:
Three associated with each of the alphas in the cell with two alpha
possibilities.
Here's an IF function approach that seems to work OK.
=IF(A1="d",IF(B1="a",5%,IF(B1="b",6%,IF(B1="c",7%,FALSE))),IF(A1="e",I
F(B1="a",8%,IF(B1="b",9%,IF(B1="c",10%,FALSE)))))
To make it more explicit, I've defaulted to FALSE if the input cells
are not the required alpha characters. You could use "" or 0 if
preferred.
If this gets much more complicated then VLOOKUP or other approaches
might be better.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
alpha characters appears in a third cell"
I assume that there are in fact six rates:
Three associated with each of the alphas in the cell with two alpha
possibilities.
Here's an IF function approach that seems to work OK.
=IF(A1="d",IF(B1="a",5%,IF(B1="b",6%,IF(B1="c",7%,FALSE))),IF(A1="e",I
F(B1="a",8%,IF(B1="b",9%,IF(B1="c",10%,FALSE)))))
To make it more explicit, I've defaulted to FALSE if the input cells
are not the required alpha characters. You could use "" or 0 if
preferred.
If this gets much more complicated then VLOOKUP or other approaches
might be better.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
one of three alpha characters appears in another cell and which of twoWayne said:
alpha characters appears in a third cell"
G
Guest
I'm not too smart this time Norm. I have put the formula in as follows: =IF(C17="I",IF(D17="F",G13)(C17="T",IF(D17="F",G14)(C17="C",IF(D17=F,G15)))) and I,m getting "false". I should get the result equal to the value that is in "G15" since I have a "C"in "C17" and an"F" in "D17". Does this make my problem clearer? Have I completely misunderstood you
I appreciate your patience and the help. (what is meant by MVP Excel?
Wayne Cameron Brockville, Ontario Canada
I appreciate your patience and the help. (what is meant by MVP Excel?
Wayne Cameron Brockville, Ontario Canada
R
Ragdyer
Try this:
=IF(D17="F",IF(C17="I",G13,IF(C17="T",G14,IF(C17="C",G15,"NO Match"))))
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
and I,m getting "false". I should get the result equal to the value that is
in "G15" since I have a "C"in "C17" and an"F" in "D17". Does this make my
problem clearer? Have I completely misunderstood you?
=IF(D17="F",IF(C17="I",G13,IF(C17="T",G14,IF(C17="C",G15,"NO Match"))))
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
=IF(C17="I",IF(D17="F",G13)(C17="T",IF(D17="F",G14)(C17="C",IF(D17=F,G15))))Wayne Cameron said:
and I,m getting "false". I should get the result equal to the value that is
in "G15" since I have a "C"in "C17" and an"F" in "D17". Does this make my
problem clearer? Have I completely misunderstood you?
N
Norman Harker
Hi Wayne!
The following (corrected) versions seems to work OK but it might not
be what you want
=IF(C17="I",IF(D17="F",G13),IF(C17="T",IF(D17="F",G14),IF(C17="C",IF(D
17="F",G15))))
Here's what you had so you can spot the differences:
=IF(C17="I",IF(D17="F",G13)(C17="T",IF(D17="F",G14)(C17="C",IF(D17=F,G
15))))
A couple of commas, insertion of two IFs and making sure F was "F"
C17 can be I, T, or C.
If C17 is none of those, you'll get FALSE
It C17 is I then if D17 is F you get G13 but if D17 is not F you get
FALSE
If C17 is T then if D17 is F you get G14 but if D17 is not F you get
FALSE
If C17 is C then if D17 is F you get G15 but if D17 is not F you get
FALSE
I find that decision trees are quite useful for sorting these IF
functions out but it is a matter of taste and some regard it as a
brute force approach.
An MVP? Here's the official blurb from Microsoft:
http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs
Extract starts >>
Here are some frequently asked questions and answers. Feel free to
contact the Microsoft MVP Program for any questions, comments, or
suggestions about the MVP Program.
What is the Microsoft MVP award?
The Microsoft Most Valuable Professional (MVP) Award is an annual
award that is given to outstanding members of Microsoft's peer-to-peer
communities.
Why does the award exist?
Microsoft believes that a robust, interactive user community is key to
helping customers maximize the solutions and benefits from their
software investments. The MVP Award is the way Microsoft recognizes
those participants who have made a highly positive impact in the
technical and product communities they participate in. Microsoft wants
community participants and leaders to know that their contributions
are greatly appreciated. The MVP Award exists as a way to reach out to
and thank outstanding members for their past participation and
willingness to help others in these communities.
How does Microsoft award the MVPs?
An internal committee of professionals from various groups at
Microsoft selects the annual MVP Award winner from a group of
nominees.
Are Microsoft MVPs experts in all Microsoft technologies and products?
No. Although many MVPs have in-depth knowledge of more than one
product or technology, none of them are experts in all Microsoft
technologies or products.
Do Microsoft MVPs receive any payment from Microsoft?
No. Microsoft does provide a small award of software, but MVPs do not
receive any monetary payment from the Microsoft MVP Program.
How long does the Microsoft MVP Award last?
The MVP Award is given each year for an individual past participation
in a Microsoft technical community. The period between awards is
considered an award year during which award recipients are free to
refer to themselves as a Microsoft MVP and enjoy all other privileges
included with the award.
What does Microsoft expect of its MVPs?
Because the MVP Award is an award-based program whose criteria are
based on past contributions, Microsoft has no expectations of MVPs
beyond the expectations of courtesy, professionalism, and adherence to
the community rules that we ask of all Microsoft community members.
How can I become a Microsoft MVP?
There is no set formula, script, or plan you can follow that will
guarantee that you will receive an award. If you have recently begun
participating in a Microsoft peer-based community, do not expect to
move into a leadership role overnight. Earning the respect of your
peers and earning recognition from Microsoft takes time. If you have
been participating for a while, you may already be on your way to
achieving MVP status.
<< Extract Ends
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
=IF(C17="I",IF(D17="F",G13)(C17="T",IF(D17="F",G14)(C17="C",IF(D17=F,G
15)))) and I,m getting "false". I should get the result equal to the
value that is in "G15" since I have a "C"in "C17" and an"F" in "D17".
Does this make my problem clearer? Have I completely misunderstood
you?
The following (corrected) versions seems to work OK but it might not
be what you want
=IF(C17="I",IF(D17="F",G13),IF(C17="T",IF(D17="F",G14),IF(C17="C",IF(D
17="F",G15))))
Here's what you had so you can spot the differences:
=IF(C17="I",IF(D17="F",G13)(C17="T",IF(D17="F",G14)(C17="C",IF(D17=F,G
15))))
A couple of commas, insertion of two IFs and making sure F was "F"
C17 can be I, T, or C.
If C17 is none of those, you'll get FALSE
It C17 is I then if D17 is F you get G13 but if D17 is not F you get
FALSE
If C17 is T then if D17 is F you get G14 but if D17 is not F you get
FALSE
If C17 is C then if D17 is F you get G15 but if D17 is not F you get
FALSE
I find that decision trees are quite useful for sorting these IF
functions out but it is a matter of taste and some regard it as a
brute force approach.
An MVP? Here's the official blurb from Microsoft:
http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs
Extract starts >>
Here are some frequently asked questions and answers. Feel free to
contact the Microsoft MVP Program for any questions, comments, or
suggestions about the MVP Program.
What is the Microsoft MVP award?
The Microsoft Most Valuable Professional (MVP) Award is an annual
award that is given to outstanding members of Microsoft's peer-to-peer
communities.
Why does the award exist?
Microsoft believes that a robust, interactive user community is key to
helping customers maximize the solutions and benefits from their
software investments. The MVP Award is the way Microsoft recognizes
those participants who have made a highly positive impact in the
technical and product communities they participate in. Microsoft wants
community participants and leaders to know that their contributions
are greatly appreciated. The MVP Award exists as a way to reach out to
and thank outstanding members for their past participation and
willingness to help others in these communities.
How does Microsoft award the MVPs?
An internal committee of professionals from various groups at
Microsoft selects the annual MVP Award winner from a group of
nominees.
Are Microsoft MVPs experts in all Microsoft technologies and products?
No. Although many MVPs have in-depth knowledge of more than one
product or technology, none of them are experts in all Microsoft
technologies or products.
Do Microsoft MVPs receive any payment from Microsoft?
No. Microsoft does provide a small award of software, but MVPs do not
receive any monetary payment from the Microsoft MVP Program.
How long does the Microsoft MVP Award last?
The MVP Award is given each year for an individual past participation
in a Microsoft technical community. The period between awards is
considered an award year during which award recipients are free to
refer to themselves as a Microsoft MVP and enjoy all other privileges
included with the award.
What does Microsoft expect of its MVPs?
Because the MVP Award is an award-based program whose criteria are
based on past contributions, Microsoft has no expectations of MVPs
beyond the expectations of courtesy, professionalism, and adherence to
the community rules that we ask of all Microsoft community members.
How can I become a Microsoft MVP?
There is no set formula, script, or plan you can follow that will
guarantee that you will receive an award. If you have recently begun
participating in a Microsoft peer-based community, do not expect to
move into a leadership role overnight. Earning the respect of your
peers and earning recognition from Microsoft takes time. If you have
been participating for a while, you may already be on your way to
achieving MVP status.
<< Extract Ends
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
follows:Wayne Cameron said:
=IF(C17="I",IF(D17="F",G13)(C17="T",IF(D17="F",G14)(C17="C",IF(D17=F,G
15)))) and I,m getting "false". I should get the result equal to the
value that is in "G15" since I have a "C"in "C17" and an"F" in "D17".
Does this make my problem clearer? Have I completely misunderstood
you?
G
Guest
Thanks again Norm. It works. Today was my first try at user group participation. The experience was very helpful and I'll certainly use it again when I am unable to resolve questions myself. In my humble opinion you are very deserving of the MVP award. Maybe someday I'll be good enough at Excel to help out even if not good enough for an award. Thanks again.
Wayne
Wayne
N
Norman Harker
Hi Wayne!
Thanks for thanks is always appreciated and it confirms to Google
searchers that something works.
Even after a short time you'll be able to answer some queries and by
participating in the newsgroups you'll be amazed at how much you
learn. You'll also make good friends.
Seasons Greetings
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
it again when I am unable to resolve questions myself. In my humble
opinion you are very deserving of the MVP award. Maybe someday I'll be
good enough at Excel to help out even if not good enough for an award.
Thanks again.
Thanks for thanks is always appreciated and it confirms to Google
searchers that something works.
Even after a short time you'll be able to answer some queries and by
participating in the newsgroups you'll be amazed at how much you
learn. You'll also make good friends.
Seasons Greetings
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
participation. The experience was very helpful and I'll certainly useWayne Cameron said:
it again when I am unable to resolve questions myself. In my humble
opinion you are very deserving of the MVP award. Maybe someday I'll be
good enough at Excel to help out even if not good enough for an award.
Thanks again.