Can anyone help me out with this question? Nobody at the Mr. Excel forum seems interesting in answering.

I made up a simplified dummy sheet based on your raw data and messed around with it for a while and I couldn't get the pivot table to do what you wanted. I thought for sure there would be a calculation available for that but it seems there isn't.

I feel like I just have to plot them manually and THEN create the Pivot chart afterwards. But I may be able to show the values initially in the table as % from previous and then chart it. I'll figure it out, for now it does enough of what I need. Thank you for the help.

I think I would do a pivot table first to get the data for your leads + leads to conversions per month. Then copy that data and paste it somewhere else. Add a column to do your percent conversions calculation.

Then insert a line chart from that to plot the percent conversions per month that you calculated.

Yep, that's what I was thinking as well by manual conversion. I'll play with it. Here's another issue I'm hoping I can solve without needing VBA's of some sort, but I doubt it.

I have a list of data all in a single column. Of that data, there's about 10 types of cells that I want the information from. And more importantly, I need to keep it in order.

Here's a quick example:



Each "set" of information is specific to a single "group", although as far as the data is concerned, it's undefined. I want to remove ALL data that I don't need. For instance, all I want to know is the person's name, their height and weight, if available. How can I do that the easiest way?

Also note that the data isn't constant as there's random anomalies like "Hat:" coming in once, but not again. The only constant is that "Food:" is ALWAYS going to appear as the beginning set and signal the start of a new "set."

I'm an idiot. I can just find all for the phrase I want and paste in the next column and go down the line.

Looks like it's been answered on Mr Excel - or am I missing something?

I have a cell that concatenates a sequence of Y's and N's based on the contents of other cells from the corresponding row. I'd like to now convert that concatenated sequence into a number that is a sequence of 1's and 0's where Y=1 and N=0.

Any ideas?

=SUBSTITUTE(SUBSTITUTE(A1,"Y",1),"N",0)

I didn't know that the command SUBSTITUTE existed. I am now genuinely excited, this saves me days of work on my fantasy model.

I have three sets of data containing a payee and an amount. I want to see amounts lined up to payees

Payee, amt 1, 2,3

I use vlookup to accomplish this, however, the payees change as well.

Can I easily identify and add payees from the second and third set that aren't in the first?

someone else may understand your question but i don't. an example would be useful.

That's what I was thinking.

Isn't it a simple SUMIFS function?

I think what you mean is this:
- you have three tabs with Payee and Amount columns,
- some but not all of the payees are duplicated between tabs.
- And you want to aggregate them into one sheet with a Payee Column and one amount column corresponding to each of the three tabs.

Is this right?

If so, can you just export a master payee list from your source data into a fourth tab and then use vlookup three times?

I'm not that great with excel so it's probably easier to use this example to explain what I'm trying to do:



The values in D ("Marks") change. When they do I sort them in ascending order along with the name of the person they are associated with.

I'd like to take Jack's Mark (now in D5) and copy it to W32. The problem I'm having is that Jack's Mark won't always be in D5 as it will move when it changes and gets sorted. It will always be two to the right of "Jack" if that helps us.

Can I copy Jack's Mark in W32 and have it show the correct value even after it changes and gets sorted in the column D?

Hope I explained that okay?

Yes, you can use VLOOKUP("Jack",$B$2:$D$20,3,FALSE)

"Jack" is the value that Excel will look for in the leftmost column of the table;
$B$2:$D$20 are the coordinates of the entire table (adjust for your actual spreadsheet);
3 because you are looking for the value from the 3rd column of the table;
FALSE at the end is necessary for some reason that I don't remember.

Alternatively, enter Jack's name in cell V32 (or somewhere else) and replace "Jack" with V32 in the VLOOKUP formula. You can enter the names of the other students in column V and copy the VLOOKUP formula down as well (this will work provided you use the dollar signs in the VLOOKUP to specify the coordinates of your table).

Thought I'd be able to use the info from the example and apply it to my spreadsheet. Guess not

So, here's the exact location of the data I'm trying to work with. If you could let me know what formula to use, that would be great!

I have a list of names in B33-B45. I have the scores associated with those names next to them in D33-D45.

I have a separate area in my spreadsheet with the same list of names (in a different order) in B4-B16.

I would like to copy the value for the scores for each person found in D33-D45 and copy those scores in the correct place for the correct person next to the names in the list found at B3-B16. These scores should be copied to C3-C16 right next to the correct name.

What formula can I use?


Thanks so much!!!

in cell C3:

VLOOKUP(B3,$B$33:$D$45,3,FALSE)

and copy it down.

INDIRECT confuses me. I have a table of data where the columns are sequential dates, let's say there's only one row. I want to take an average of three sequential columns, which of course I can do with:

=AVERAGE(W24:Y24)

But I want to be able to vary the column that I start the three-date-average depending on the contents of a lookup cell. If that lookup cell is 20, then I start at column W. If the lookup cell is 21, then I start at X and so on.

I can make a list for vlookup that equates 20 and 21 etc with W and X and so on, but I don't know how to get from there to a general three-date-average formula. I assume it involves INDIRECT but I don't really get how that function works still. Does anyone know how I should do it?

AVERAGE(OFFSET(A24,0,[location of lookup cell],1,3))

Nice, thanks. I didn't know OFFSET existed.

Sorry it's taken me so long to get back.

You are mostly correct, except source data isn't a master list. Can I create a master list from three individual lists eliminating duplicate values?

You can copy and paste three times and then use the Remove Duplicates command (under the Data tab).

To make it happen automatically I think you would need a Macro, I don't think it is possible using in-cell formulas.

http://www.get-digital-help.com/2009...om-one-column/

That should work