Posted by
Message
yankeeundercover
Army Fan
HMWHC® (via NOLA)
Member since Jan 2010
34447 posts

*SUPER* complex excel formula help.
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.



Layabout
UNO Fan
Baton Rouge
Member since Jul 2011
11082 posts

re: *SUPER* complex excel formula help.
A few lines of programming code would handle that nicely. A spreadsheet is the wrong tool.


Replies (0)
Replies (0)
30
SlapahoeTribe
LSU Fan
Tiger Nation
Member since Jul 2012
6350 posts
 Online 

re: *SUPER* complex excel formula help.
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


Replies (0)
Replies (0)
20
seawolf06
LSU Fan
Raleigh, NC
Member since Oct 2007
6729 posts

re: *SUPER* complex excel formula help.
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.


Replies (0)
Replies (0)
20
foshizzle
LSU Fan
Washington DC metro
Member since Mar 2008
40109 posts

re: *SUPER* complex excel formula help.
I'm sure VLOOKUP is involved in the solution somehow.


Replies (0)
Replies (0)
10
gamatt53
Georgia Fan
Member since Nov 2010
4068 posts

re: *SUPER* complex excel formula help.
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 at 9:32 am


Replies (0)
Replies (0)
10
Tshiz
LSU Fan
Oxford
Member since Jul 2013
2027 posts

re: *SUPER* complex excel formula help.
Example of values you want to use, then ill write you an if statement


yankeeundercover
Army Fan
HMWHC® (via NOLA)
Member since Jan 2010
34447 posts

re: *SUPER* complex excel formula help.
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?


SlapahoeTribe
LSU Fan
Tiger Nation
Member since Jul 2012
6350 posts
 Online 

re: *SUPER* complex excel formula help.
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?


bobaftt1212
Virginia Tech Fan
Hills of TN
Member since Mar 2013
1236 posts

re: *SUPER* complex excel formula help.
nested if or preferably VBA code


lynxcat
Rutgers Fan
Member since Jan 2008
19803 posts

re: *SUPER* complex excel formula help.
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.


lynxcat
Rutgers Fan
Member since Jan 2008
19803 posts

re: *SUPER* complex excel formula help.
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.


Replies (0)
Replies (0)
00
SlapahoeTribe
LSU Fan
Tiger Nation
Member since Jul 2012
6350 posts
 Online 

re: *SUPER* complex excel formula help.
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


Replies (0)
Replies (0)
00
FreddieMac
LSU Fan
Baton Rouge
Member since Jun 2010
6185 posts

re: *SUPER* complex excel formula help.
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


southernelite
McNeese State Fan
Dallas
Member since Sep 2009
50062 posts

re: *SUPER* complex excel formula help.
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")


FreddieMac
LSU Fan
Baton Rouge
Member since Jun 2010
6185 posts

re: *SUPER* complex excel formula help.
that is probably a macro and not a formula


Replies (0)
Replies (0)
00
antiventura
Member since Aug 2007
47 posts

re: *SUPER* complex excel formula help.
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.


Replies (0)
Replies (0)
00
CubsFanBudMan
Chicago Cubs Fan
Member since Jul 2008
3566 posts

re: *SUPER* complex excel formula help.
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".



Replies (0)
Replies (0)
00
first pageprev pagePage 1 of 1next pagelast page
refresh

Back to top

logoFollow TigerDroppings for LSU Football News
Follow us on Twitter, Facebook and Instagram to get the latest updates on LSU Football and Recruiting.

FacebookTwitterInstagram