- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Excel formula question
Posted on 12/19/12 at 9:28 am
Posted on 12/19/12 at 9:28 am
I want to enter a value in column A and have a corresponding value populate Column B. For instance if I enter "XYZ" in A1, then I want $40 in B1. Or if I enter "ABC" in A1, then I want $50 in B1. How do I do this.
The "IF" function is not doing it for me.
TIA
The "IF" function is not doing it for me.
TIA
Posted on 12/19/12 at 9:45 am to Motorboat
I use Conditional formatting on cells that takes care of the programming for me. Unfortunately, being a technical guy, my excel knowledge is on a novice level.
Posted on 12/19/12 at 9:49 am to RickAstley
quote:
RickAstley
Never gonna give you up, never gonna let you down. Thanks anyway.
Posted on 12/19/12 at 9:54 am to Motorboat
Vlookup with a table of abc and xyz sorted alphabetically (descending) with their corresponding values adjacent
Posted on 12/19/12 at 10:01 am to Motorboat
How are "IF" formulas not working for you on this? Are you putting quotation marks around the text you want to see in Column A and also formatting cells in Column A as general?
Posted on 12/19/12 at 10:05 am to BennyAndTheInkJets
quote:
How are "IF" formulas not working for you on this? Are you putting quotation marks around the text you want to see in Column A and also formatting cells in Column A as general?
I think I may have gotten my quotes in the wrong place. Let me try as suggested in the link and I'll report. Thx
ETA:
From the Link:
Question: In Microsoft Excel, I need a formula for the following:
IF cell A1= PRADIP then value will be 100
IF cell A1= PRAVIN then value will be 200
IF cell A1= PARTHA then value will be 300
IF cell A1= PAVAN then value will be 400
Answer: You can write an IF statement as follows:
=IF(A1="PRADIP",100,IF(A1="PRAVIN",200,IF(A1="PARTHA",300,IF(A1="PAVAN",400,""))))
My formula is this:
=IF(B2="97001",185,IF(B2="97110",48,IF(B2="97104",32,IF(B2="97010",25,IF(B2="97140",59""))))
What's my problem??
This post was edited on 12/19/12 at 10:17 am
Posted on 12/19/12 at 10:24 am to Motorboat
=IF(A1="XYZ";40;IF(A1="ABC";50;""))
Format the cells with the formula as currency.
Format the cells with the formula as currency.
This post was edited on 12/19/12 at 10:30 am
Posted on 12/19/12 at 10:29 am to Motorboat
Have you tried using a vertical lookup table?
Or is it just 2 possibilities
Or is it just 2 possibilities
Posted on 12/19/12 at 10:34 am to davesdawgs
quote:
Format the cells with the formula as currency.
Done and comes back as "false". WTH? Does the fact that my conditional number is not text (a number) matter?
This post was edited on 12/19/12 at 10:36 am
Posted on 12/19/12 at 10:35 am to Motorboat
quote:
My formula is this:
=IF(B2="97001",185,IF(B2="97110",48,IF(B2="97104",32,IF(B2="97010",25,IF(B2="97140",59""))))
What's my problem??
Your problem is, it's unmaintainable and generalizes poorly. What if you had 100 possibilities?
You need to use a lookup table as previously mentioned. Basically, you're trying to implement an associative array or map, which are the computer language terms for it. If Excel hasn't finally provided high-level support for this concept, the traditional way to do it would be to have a column of key values and an adjacent column of item values, each row in this range making up a pair (key, item). Then use the appropriate lookup function on the keys to retrieve the adjacent item.
Posted on 12/19/12 at 10:38 am to Motorboat
quote:There appears to be a comma missing after the "59" and before the "".
=IF(B2="97001",185,IF(B2="97110",48,IF(B2="97104",32,IF(B2="97010",25,IF(B2="97140",59""))))
Posted on 12/19/12 at 10:47 am to Spock's Eyebrow
quote:
You need to use a lookup table as previously mentioned.
You guys rock. got it. Had no idea this was a function. thanks!
Posted on 12/19/12 at 5:53 pm to Motorboat
quote:
=IF(B2="97001",185,IF(B2="97110",48,IF(B2="97104",32,IF(B2="97010",25,IF(B2="97140",59""))))
As the others said, having a lookup table is best.
But if you have to do it this way instead, your formula shouldn't have all the numbers quoted like that unless cell B2 truly is a text value.
For example, if you enter the number 97110 in cell B2 your formula will not match anything. If you enter the string '97110 then it will return the number 48.
After you put a comma between the 59 and the two consecutive double quotes, that is.
Popular
Back to top
Follow TigerDroppings for LSU Football News