Creating new variables in Excel (newbie)
Hello, I have been tasked to perform the following in Excel:
• Create a numeric variable that counts the total seasons of purchase through Spring 07.
• Create a variable that indicates whether customer is a multichannel buyer or not
• Create a variable that indicates whether or not the customer has gift dollars or not
I am fairly new to Excel, so this is quite challenging for me. Any help would be greatly appreciated.
Last edited by dtimm13; 05-03-2021 at 09:34 PM. Reason: Sample workbook link
I’m not sure about your post title, but I’m not sure it follows with the form rules such as rule #1, however that will be up to a moderator or admin to decide.
Perhaps you noticed the yellow banner at the top of the post, if you upload a sample workbook of the data it doesn’t have to be all of it with expected results you can probably get more help.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Updated, thank you.
without the workbook, it's hard to tell exactly what you want.
But for the first one
Countif / Countifs can count purchases in "spring 07" depending if you want to narrow it down with another variable or not.. if you are only counting "Spring 07"
=Countif(G:G,G2) - G:G is referencing only one column the "G2" is referencing the number of times that name comes up in that column.
=Countifs(G:G,G2,E:E,E2) - This narrows it down to counting the number of times a text comes up in G:G and the number of times a Text comes up in E:E
If the column is a "true/false" for "multichannel buyer a simple if statement like =if(G1="Multichannel",TRUE,FALSE) if the column header is "Multichannel" and the column is filled with Y's and N's Then =if(G1="Y",TRUE,FALSE).
Same with gift dollars. you can use the same if statement =if(G1="gifted dollars",TRUE,FALSE)
If you have a string like you posted above RetS07Dollars = 2007 Spring Retail Dollars
You may need to do a "Find" formula to break out the variables into their own columns.
In which case I would need to know all combinations of the possible "RetS07Dollars" (for example are they all the same character length, is it always in the same order, what does Summer look like and would "Fall" be RetF06Dollars? so many questions.
The workbook looks really simple.
With the data you have given.
in column H =SUM(C2,E2,G2) would give you all Spring 2007 sales totals (drag the formula down)
Nothing tells me what a "multichannel" Customer is.. There is no formula with the data you have that tells Excel what a multichannel is...
They all look like "Gifted Dollars" based on the headers.
So you would in Column I Say "Yes" or True etc. for each ID.
there are a lot of questions I have for you.
1) is this the way you are going to receive the data or is this the way you want to structure it?
2) you note that 2004 through 2006 data will also be included, in the same columns with different headers separating them or in additional columns of their own?
3) will the customer ID remain the same for additional periods and if the additional periods will be in additional columns will the customer by ID just have more columns (like fall 2007 retail in col B and Fall 2006 retain being in, for example col H of the same row for customer 22120)?
4) what are your expected results for customer 22120 for example?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks