- 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
*SUPER* complex excel formula help.
Posted on 1/19/16 at 1:04 am
Posted on 1/19/16 at 1:04 am
If the value in Column A is "1". (variable)
And the Value in Column B is "2". (variable)
If Column B is *NOT* "2" (but is "~#~") then change "B" to "~%~"
Basically, I need Column A to determine what Column B needs to be... If it's "2" in Column B, and Column A *isn't* "1", then no matter what it is, change every other Column B value to the value associated with whatever column B is that *isn't* "2".
This is ridiculous, I know. It's even hard to follow for me
note: "~#~" and "~%~" is any number but 2 and a complete variable
The only real world analogy my and three of my friends could come up with is,
Action___Recipient
Call_____Me
Text_____Adam
Signal___Me
Slap_____Chris
Call_____Me
Kill_____Me
Slap_____Chris
Signal___Dave
Kill_____Me
Push_____Dave
Basically, if it's not "Me" in the 'recipient' column, then use the action to determine whatever "Me" should be replaced with based on the Action column.
And the Value in Column B is "2". (variable)
If Column B is *NOT* "2" (but is "~#~") then change "B" to "~%~"
Basically, I need Column A to determine what Column B needs to be... If it's "2" in Column B, and Column A *isn't* "1", then no matter what it is, change every other Column B value to the value associated with whatever column B is that *isn't* "2".
This is ridiculous, I know. It's even hard to follow for me
note: "~#~" and "~%~" is any number but 2 and a complete variable
The only real world analogy my and three of my friends could come up with is,
Action___Recipient
Call_____Me
Text_____Adam
Signal___Me
Slap_____Chris
Call_____Me
Kill_____Me
Slap_____Chris
Signal___Dave
Kill_____Me
Push_____Dave
Basically, if it's not "Me" in the 'recipient' column, then use the action to determine whatever "Me" should be replaced with based on the Action column.
7
Posted on 1/19/16 at 5:31 am to yankeeundercover
A few lines of programming code would handle that nicely. A spreadsheet is the wrong tool.
Posted on 1/19/16 at 5:59 am to yankeeundercover
Hum... got me reaching back a few years on this one. I'd go with a an IF function if you're only concerned with two variables, but if you have multiple variables then maybe building a hidden chart of if/not_if results would be easier. Either way, if you're hoping to insert something into the column yourself then you're going to have to use a third column to display results.
I'm working on knowledge that is a few years old here and those days included lots of scotch and weed. so...
This might help Excel Variable Chart
I'm working on knowledge that is a few years old here and those days included lots of scotch and weed. so...
This might help Excel Variable Chart
Posted on 1/19/16 at 6:45 am to yankeeundercover
Give us an actual example of what you're trying to do.
Is Column B given or are you trying to calculate it?
Either way, I think your answer is a nested If statement.
Is Column B given or are you trying to calculate it?
Either way, I think your answer is a nested If statement.
Posted on 1/19/16 at 9:09 am to yankeeundercover
I'm sure VLOOKUP is involved in the solution somehow.
Posted on 1/19/16 at 9:29 am to yankeeundercover
not super complex. Just a nested IF statement with only one condition it appears. Depending on what exactly you are wanting to achieve an lookup functions or index/match may work as well if you have pick cells. Give us a better example and you may get some help as this is a super vague request. This post was edited on 1/19/16 at 9:32 am
Posted on 1/19/16 at 9:57 am to yankeeundercover
Example of values you want to use, then ill write you an if statement
Posted on 1/20/16 at 8:17 am to Tshiz
What I'm trying to accomplish:
If Col.B *ISN'T* X, and is Y, then change Col.A to Col.A+Y
Tons of variables. I'm having a hard time wrapping my head around how do describe what I'm trying to do...
We'll call them text messages...
If Col.A is a phone number, and Col.B is "Me" and Col.C is "Sent"...
And another line is Col.A phone number, a "Friend's name", and Col.C is "Incoming"...
I'm trying to change Col.A on all the "Me" instances that reference the Col.A for "Friend's name"...
Basically, all of Col.A has to be the same "phone number", but dependent on the "Friend's name", but associated with "Me"...
...does that make any god damned sense?
If Col.B *ISN'T* X, and is Y, then change Col.A to Col.A+Y
Tons of variables. I'm having a hard time wrapping my head around how do describe what I'm trying to do...
We'll call them text messages...
If Col.A is a phone number, and Col.B is "Me" and Col.C is "Sent"...
And another line is Col.A phone number, a "Friend's name", and Col.C is "Incoming"...
I'm trying to change Col.A on all the "Me" instances that reference the Col.A for "Friend's name"...
Basically, all of Col.A has to be the same "phone number", but dependent on the "Friend's name", but associated with "Me"...
...does that make any god damned sense?
Posted on 1/20/16 at 8:36 am to yankeeundercover
quote:
...does that make any god damned sense?
No. When you have this finished, what are you hoping to be able to enter and what is the result you want?
Are you wanting to enter something into column B and have the computer change something in column A? And what it changes column A to will depend on what's in C?
Posted on 1/20/16 at 12:20 pm to SlapahoeTribe
nested if or preferably VBA code
Posted on 1/20/16 at 1:34 pm to bobaftt1212
Nested if should be able to handle this without much trouble.
.... But I still don't know exactly what he is trying to do.
He needs to just post a picture of sample data in a spreadsheet.
.... But I still don't know exactly what he is trying to do.
He needs to just post a picture of sample data in a spreadsheet.
Posted on 1/20/16 at 1:35 pm to lynxcat
My only concern is whether what he is trying to do will create a circular reasoning error. I can't tell what is static and what is an output.
Posted on 1/20/16 at 5:20 pm to lynxcat
quote:
But I still don't know exactly what he is trying to do.
Yeah... I'm lost.
quote:
He needs to just post a picture of sample data in a spreadsheet.
Bingo
Posted on 1/21/16 at 10:59 am to yankeeundercover
quote:
...does that make any god damned sense?
No, but stop and try to break down the pieces of the algorithm you are building.
Obviously there is conditional logic involved so, Excel does have an if. Also, you have an and, so your formula or code needs to be compound
IF Col.B <> X AND Col.B = Y then
=Col.A + Y
endif
paste formula down cells
Posted on 1/21/16 at 3:38 pm to FreddieMac
Okay, so here is what he wants to do here...
Column A | Column B
555-555-5555 | Me
555-555-5555 | Me
555-555-5555 | Adam
333-333-3333 | Me
333-333-3333 | Brittany
So he wants to write a formula where all instances of "Me" associated with the phone number in Column A equal the names associated with those numbers which are located only in certain instances of B. (Which are tied to incoming text messages and the me are outgoing text messages, but the outgoing messages need the recipients name attached, not the sender's name, referenced here as "Me")
Column A | Column B
555-555-5555 | Me
555-555-5555 | Me
555-555-5555 | Adam
333-333-3333 | Me
333-333-3333 | Brittany
So he wants to write a formula where all instances of "Me" associated with the phone number in Column A equal the names associated with those numbers which are located only in certain instances of B. (Which are tied to incoming text messages and the me are outgoing text messages, but the outgoing messages need the recipients name attached, not the sender's name, referenced here as "Me")
Posted on 1/21/16 at 3:40 pm to southernelite
that is probably a macro and not a formula
Posted on 1/21/16 at 10:25 pm to southernelite
Looking at that data, have you tried the following?
1. Find and replace all instances of "Me" in Column B with "ZZZ" (something that will sort last alphabetically in a group).
2. Sort your spreadsheet by Column A first, then Column B (smallest to largest). This should put your data in the following format:
Col A. Col B.
555-5555 John Doe
555-5555 ZZZ
555-5555 ZZZ
777-7777 Rob Doe
777-7777 ZZZ
3. After that, a formula similar to the following should work in Column C:
IF(B2 <> "ZZZ", B2, C1)
Hopefully the formula is well-formed. I'm typing this on a phone.
1. Find and replace all instances of "Me" in Column B with "ZZZ" (something that will sort last alphabetically in a group).
2. Sort your spreadsheet by Column A first, then Column B (smallest to largest). This should put your data in the following format:
Col A. Col B.
555-5555 John Doe
555-5555 ZZZ
555-5555 ZZZ
777-7777 Rob Doe
777-7777 ZZZ
3. After that, a formula similar to the following should work in Column C:
IF(B2 <> "ZZZ", B2, C1)
Hopefully the formula is well-formed. I'm typing this on a phone.
Posted on 1/22/16 at 10:12 am to yankeeundercover
I may be totally off base here, but it sounds like what you're trying to do is to dump a cell phone bill into excel, then replace the phone numbers with names. If so:
1) Build a table with phone numbers in column A and names in Column B.
2) vlookup to this table to identify names.
3) if name = me, "Sent", "Received".
1) Build a table with phone numbers in column A and names in Column B.
2) vlookup to this table to identify names.
3) if name = me, "Sent", "Received".
Page 1 of 1
Back to top
Follow TigerDroppings for LSU Football News