Excel workbook test! help please

[Projfin]

Hey everyone,

So I was redirrected here by a friend for the ultimate excel information exchange. I've been a bit rough around the edges on some subjects lately, I've got this excel workbook test. Its very short, and believe it involves Vlookups, dropdown tabs etc.

I wanted to upload it and if people can complete it and send either a link back or email to Removed e-mail address - Moderator that would be great. I want to get different peoples perspectives and solving methods on this simple assignment. It shouldnt take more than 15-20 min for most people.

Thank you!

http://www.2shared.com/file/152bNSTj/Excel_Test.html

 

[sous2817]

Here is one way:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-right: 1px solid black;;">Product Name</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">A</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-right: 1px solid black;;">Month</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">Jan</td><td style="text-align: right;border-left: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">Actual</td><td style="font-weight: bold;text-align: center;;">Projected</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Channel 1</td><td style="text-align: right;;">10</td><td style="text-align: right;;">76</td></tr></tbody></table><p style="width:7.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Summary Sheet</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B6</th><td style="text-align:left">=HLOOKUP(<font color="Blue">$B$2,INDIRECT(<font color="Red">$B$1 & "!C5:N9"</font>),2,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C6</th><td style="text-align:left">=HLOOKUP(<font color="Blue">$B$2,INDIRECT(<font color="Red">$B$1 & "!C15:N19"</font>),2,0</font>)</td></tr></tbody></table></td></tr></table><br />

I just did the first Channel, but it will work for any of the sheets or months that you supply. You'll have to research the HLOOKUP function a bit to figure out how to apply it to the other channels. I'll give you a hint..probably revolves around the ,2, part of the formula.

Also, I'm sure there are other ways to go about it...but that's how I'd go about it.

 

[Projfin]

Hi sous,

thank you very much for your help so far, it is much much appreciated. indeed i do understand the difference bw the 2, 3, 4 etc being the number of rows across horizontally. but when i inserted the formula it did not work and i inserted it exactly the same and i got a #value. also will this work when the yellow highlighted area is a "list" showing the list of months and the choice of product right above it ? since this is a summary page the end user should be able to switch between product type and month freely.

 

[Projfin]

also, in your formula by referring to "C5:N9" you are referring to sheet A? or the summary sheet? maybe that is why it is not appearing for me

 

[Projfin]

ok i just figured it out, thank you very much sous. i did not know that much about the INDIRECT function and did not know "!C5:N9" and A!C5:N9 would be the difference in the situation

 

[Projfin]

So Now that i see how this works, can you explain to me what exactly the INDIRECT function does and what it means ? i inserted the row numbers as 2,3,4,5 for each channel and it worked, but what im curious about is how come
1- it does not require a definition for which channel to find and
2- if its horizontal lookup how come changing the row numbers determines the vertical placement?

 

[sous2817]

Indirect returns a reference from a text string. In this case, it's using the value in B1 (which refers to the sheet name) and turns that string in to the sheet reference. That's why all you need to do it change the value in B1 to change which sheet the formula references. To complete the indirect part, there's !C5:N9, which is the table reference so at the end, after the INDIRECT works it's magic you're left with a valid table range for HLOOKUP to work with.

Also 1) The channel is defined by the row parameter of the hlookup formula. So as you increase that number, it goes to the next channel

2) Because that's how Microsoft decided it would work.

 

[Projfin]

Sous,

thank you very much for the explanation, i completely understand the function now. thank you for that.

I wanted to ask what your interpretation on the next steps of this would be, in the workbook it asks for variance in column D, do you think that I should calculate this by lets say;

by finding the variance between each channel and the totals of all 4 channels or the variance between the actual and projected for each channel?

I want to think that it would be the variance of each channel by the total of all four channels, but calculating the variance between the actual and forcasted from each channel would also make sense as a measurement of accuracy between the two.

 

[Projfin]

Product Name B
Month Mar


Actual Projected Variance Variance %
Channel 1 61 62 0.25 0.00%
Channel 2 104 119 56.25 0.67%
Channel 3 287 98 8930.25 106.66%
Channel 4 116 106 25 0.30%

Total 568 385 8372.25


Above is an example of what I found, would this be correct?
for variance i used Varp(actualrojected), and for variance % I divided each channels variance by the variance of the variances. does that seem correct?

 

[sous2817]

I'm no statistician, but VARP seems to be a bit of an overkill when working with only 2 numbers.

I read it as the difference between actual and projected. So your first one would be a variance of 1 (62 Projected and 61 Actual) and the Variance % is 1.6.

So I'd solve the last two columns using a simple subtraction and then division rather than one of the VAR, VARP, VARA, or VARPA functions.

 

[Projfin]

You are probably most right sous!

did you get that 1.6 by downloading the file from the link i posted on my first post? so that is for B for march

thank you by the way for your input. Ive been working with excel for two years or so but more on the project feasibility side so some of these concepts ima bit rusty with but not incompetent with.