Yes, you can do that, it's just basic math in there. If you have impressions and clicks, calculating CTR is as easy as it gets. If you have installs, your conversion rate is the same. Just compare them manually and you can tell which is the more profitable ad.

This is incorrect.

This tells you the current conversion rates, but as the poster mentioned, he's looking for the statistical significance of the results he is seeing. Basically, the simplified version of the mistake you are making is: I have two standard dice, I rolled each of them twice, one had a 5 and a 6, the other a 1 and a 2, therefor the first produces higher results on average.

There's are statistical tests to let you know if how likely you are, given the observations you've got, to have different underlying distributions producing the samples. This saves you from making bad decisions on short term run bad or run good (to make it a poker analogy instead of dice. Like, if you had a couple bad runs with aces, yiu wouldn't stop playing them).

Here's a reasonable reading piece on the subject (I have only skimmed). There are a number of different tests you could use in excel, but I think that this article plus one quick round of googling will help you figure out the method that works best for your question:

https://www.optimizesmart.com/conver...y-significant/

citanul,

I was assuming he was already armed with the information to determine the right sample size and was only looking to find the values/results from the sets of data. He said he's looking "whether the difference in Click Rate between two ads is statistically significant" which to me implied he was only interested in comparing the CTRs and manually deciding, not a formula to decide for him. My bad. The link you provided is making an example of what I said, manually comparing additional points of data to determine the best course of action and not just conversion rate.

wonderkid,

Here's a link with Excel spreadsheets that I think are for what you want. I still prefer manually checking everything before whittling down to thin decisions. We utilized statistical significance for email marketing campaigns a while back and they didn't instill confidence in me. The intangibles that don't exist in the spreadsheets hold too much weight (in our scenarios) and lack of control in our testing also swayed our results.

http://www.teasley.net/calculator.html
http://www.ppchero.com/statistical-s...t-hint-you-do/

What are some examples of using the OFFSET function?

This is maybe the most famous/useful:
http://www.excel-easy.com/examples/d...med-range.html

It's one of the most useful functions.

For instance let's say i have a few years of monthly data in row 1, going across. I can have a number in cell A2 and use the formula =OFFSET(A1,0,A2), it will pull out the data from row 1, A2 number of months from the start (starting on cell A1). Let's say you have a gap pf one cell between each month's data - you can modify the formula to =OFFSET(A1,0,A2*2).

That's pretty basic. But it becomes really powerful when you use OFFSET combined with SUM. For instance =SUM(OFFSET(A1,0,A2,1,12)) will give you the sum of the 12 months starting A2 number of cells from the start - you can do the same with AVERAGE.

Building more complex formulas around this concept you can do some really impressive stuff.

Are any of you guys good at styling worksheets? I'm trying to have a clean interface, nothing busy, but my data looks like ****. I don't have a knack for design.

I'm showing month by month analysis for a few things, mainly the amount of leads/conversions we get (hard numbers) and the actual conversion rate. I'm going to do this for all 12 months. They update automatically based on another cell so this whole thing is open for playing around.

Any ideas or links would be great!

I don't super hate the framework of your design, and I am not a design pro. Here's what I would do:

1. Make the colors less garish and easy to read the numbers through. Maybe even consider losing the color except in the header row of the column.

2. Make a clearer division between the batches column groups. Something like a 2 pt black border? Possibly combine this with making the individual cell borders less prominent.

3. Make the header rows more clearly differentiated. Toy with font size/boldness. Make a bottom border on the header that is similar to the one that yiu used to break between groups of columns. Some people like to play with fancy borders like double lines between header and the rest of tables.

4. I can't navigate back to see the table because of stupid app I'm using, but put a frame around the whole table if you haven't already.

I think those things help the reader:

1. Know what the data in front of them is as quickly as possible (this is by being able to identify the headers quickly).

2. Know what data goes together as quickly as possible (you're already accomplishing this with the colors, but I think borders actually encasing the data actually work better to visually group things quickly. Easier to "see" only the framed section than only a bright yellow section.)

3. Makes it easy to see the whole thing. By this I just really mean eliminate that yellow. Don't get me wrong, I use that yellow a lot. But I use it specifically for "Holy **** look right here right now there's something unfinished or broken" type stuff.

1&2 + an ability for the reader to quickly understand the conclusions they are supposed to be able to make from the presented data are, in my opinion, the goals of data visualization. It's late on a Friday, so maybe I'm over or understating things.

Start with the pre-defined styles in Excel on the Home tab > Format as Table and/or Cell Styles, then tweak to your liking. +1 for not using that bright yellow highlighting.

Haha that's the easy way, sure. =p

I got a question:
I want to run Excel calculations in the cloud.
I want to just go ahead and upload the spreadsheet to a virtual machine and run thousands of calculations there. Anyone with an easy way to set this up? I googled and I am confused. But I know it's a 100% possible to do it in a cloud.

Thanks for the ideas. The colors weren't permanent, just showing an example of grouping the year columns together by color. I had already messed with the layer styles but they seem to only alternative between colors and not other predefined border options. I don't know much about border styling but I played around and got this. A little better, I think.

Also I just spent 10 minutes rewriting the same VLOOKUP wondering why the **** it wouldn't work. I turned formulas onto manual about 15 minutes prior. I'm a moron.

I have quite a complex poker spreadsheet in which I input daily results on each row. I copy and paste one row to the next after each session and then update the copied row with the days results.

Now that it's so heavily populated it crashes each time when I copy and paste one row to the next. The only way around this is to copy a few cells at a time rather than the whole line.

I'm surprised excel can't handle it.

Any thoughts?

Buy PokerTracker? You can also hardcode the previous results instead of keeping them a live function seeing as I doubt you'll be retroactively changing large past results.

I paste overnight data for how much every player on our site plays, then cross reference it to past play and I've been doing it for two years. Excel can handle that, and it seems unlikely that you're doing more, so I suspect something's going wrong with your PC.

No good for live poker

I keep the doc on a USB stick and update it on different PC's so it's not the PC......but now that you mention it it may well be the USB stick.

I'll download it off the stick onto a PC and see if I have the same problem.

Thanks

I also have multiple graphs on the spreadsheet but I suppose that shouldn't be an issue either.

Sorry, didn't know you meant live. If you're running it straight from the USB stick, I'd imagine that could be a problem. Also, my suggestion is not a bad one. You likely don't need the live formulas crunching numbers that won't ever change. You can always back it up and then paste in the hard coded values to see if that speeds it up.

Yeah I've previously thought about what you're suggesting but my graphs need all the previous rows.

I haven't had a chance to try the USB thing. Hopefully that works.

I'm not suggesting deleting the rows, merely changing them from live functions to a hardcoded answer. Instead of =SUM(B2/A2) or something, just copy/paste them as numbers so it's not running thousands of functions all the time. Your graphs will still work.

I'm working on kind of an arbitrage exercise for work. I have 24 closing prices per day (hourly) for 365 days. I need to find the highest, 2nd highest, 3rd highest and 4th highest closing prices (also the corresponding lowest values). The highest and lowest is easy you can just use the max and min functions in a pivot table. But the others are more challenging since you can't use the LARGE and SMALL functions in a pivot or power pivot table.

How would others go about doing this?

Can't you show the top/bottom X amount of values in a pivot table?

Yeah, what? Why can't you just sort values?

Do you need to do this via the pivot table? I assume you have a long list with a column for date, time, closing price. If you make a sheet organised by date, then you sumifs or whatever to get the list of prices per date, you can use LARGE and SMALL again.