if statement?

  • Thread starter Thread starter Michael Nagles
  • Start date Start date
M

Michael Nagles

I need to write a function that will return a specific percentage in the
target cell based on a alpha letter entry in another cell.

example: (assume cell a5 has the letter and a10 is the target cell)

if a5 = C, a10 = 10%
if a5 = T, a10 = 15%
if a5 = X, a10 = 10%
if a5 empty, a10 = ""
 
With the formula cell custom formatted as: [=0]"";General, wouldn't

=OR(A5={"C","X"})*10%+(A5="T")*15%

suffice?
 
One way:

To meet your specific requirement:

=IF(A5="","",IF(OR(A5="C",A5="X"),10%,IF(A5="T",15%,"Wrong Letter")))


More generally (allowing you to easily add/delete/modify letters and
values):

=IF(A5="","",IF(ISNA(MATCH(A5,{"C","T","X"},FALSE)),"Wrong Letter",
LOOKUP(A5,{"C","T","X"},{0.1,0.15,0.1}))

You could also replace the Lookup() with a VLOOKUP on a table somewhere
in your workbook.


J K
1 C 0.1
2 T 0.15
3 X 0.1

with

=IF(A5="","",IF(ISNA(MATCH(A5,J1:J3,FALSE)),"Wrong Letter", VLOOKUP(A5,
J:K,2,FALSE))
 
Don't know if it's critical, but this formula returns 0 if anything
other than "C","X" or "T" is input into A5.
 
Set up to return 0 intentionally and a 0 result formatted to display as
blank.

JE McGimpsey said:
Don't know if it's critical, but this formula returns 0 if anything
other than "C","X" or "T" is input into A5.

Aladin Akyurek said:
With the formula cell custom formatted as: [=0]"";General, wouldn't

=OR(A5={"C","X"})*10%+(A5="T")*15%

suffice?
Click to expand...
Click to expand...
 
Understand. My point was the OP said
if a5 empty, a10 = ""
Click to expand...

while with your formula, if A5 is empty, A10=0, but *displays* a blank.

That may be sufficient for the OP, but may not be if a follow-on formula
uses something like:

=IF(A10="",foo,bar)

Also, the OP didn't specify what the result should be if a value other
than C,T or X were entered - your formula returns 0, my examples return
a string. I don't have the slightest clue whether either or both would
be acceptable.
 
Back
Top