Microsoft Excel Q&A

[Kryzz]

Help needed people please!!

I have a series of monthly returns going back 20 odd years, what I would like to do is extract single months out of that 20 years worth of data, with the end goal trying find what the returns are for a particular month (I'm trying to guage particularly strong months), without doing it manually/filters etc, ie:

September 2012 - 5%
September 2011 - 3%
Average September return...x%

I've been mucking around with pivot tables and histograms, but cannot seem to crack it, any ideas?? Hopefully this can assist everyone. Spreadsheet attached.

Thanks!

 

[skyQuake]

Help needed people please!!

I have a series of monthly returns going back 20 odd years, what I would like to do is extract single months out of that 20 years worth of data, with the end goal trying find what the returns are for a particular month (I'm trying to guage particularly strong months), without doing it manually/filters etc, ie:

September 2012 - 5%
September 2011 - 3%
Average September return...x%

I've been mucking around with pivot tables and histograms, but cannot seem to crack it, any ideas?? Hopefully this can assist everyone. Spreadsheet attached.

Thanks!

First, extract the month from the date.

Month(A1) will return 8 since it is aug etc

So its a simple matter of:

=if(Month(A1)=9,F1,"")

And drag it down.

 

[pixel]

Help needed people please!!

I have a series of monthly returns going back 20 odd years, what I would like to do is extract single months out of that 20 years worth of data, with the end goal trying find what the returns are for a particular month (I'm trying to guage particularly strong months), without doing it manually/filters etc, ie:

September 2012 - 5%
September 2011 - 3%
Average September return...x%

I've been mucking around with pivot tables and histograms, but cannot seem to crack it, any ideas?? Hopefully this can assist everyone. Spreadsheet attached.

Thanks!

Ever tried conditional array formulas? You type the formula as shown, then use Ctrl-Shift-Enter instead of Enter alone:



Results:



and the spreadsheet itself (I changed addressing to R1C1 format for easier programming)

View attachment spreadsheet.xlsx

 

[pixel]

and the spreadsheet itself (I changed addressing to R1C1 format for easier programming)

sorry the first sheet was missing the middle formula - use this one: View attachment spreadsheet.xlsx

 

[skyQuake]

Ever tried conditional array formulas? You type the formula as shown, then use Ctrl-Shift-Enter instead of Enter alone:

View attachment 54170

Results:

View attachment 54169

and the spreadsheet itself (I changed addressing to R1C1 format for easier programming)

View attachment 54171

+1 for array. While it does look harder, mastering it is VERY useful

 

[Kryzz]

Thanks guys, useful info here.

Will have to try and source some free online excel resources I think to cover the above.

 

[pixel]

Thanks guys, useful info here.

Will have to try and source some free online excel resources I think to cover the above.

http://www.excelforum.com/tips-and-tutorials/623034-array-formulas-in-excel-2007-a.html

or

http://spreadsheets.about.com/od/a/g/array_def.htm

 

[Uncle Festivus]

I have 2 tables

- 1st table (t1) contains 2 columns, item code and description, item code is unique
- 2nd table (t2) also has 2 columns as t1 - it has multiple non unique item codes but description is blank

For each row in (t2) I need to first match the item code from (t2) with item code from (t1) then fill (t2) description with the (t1) description

essentially updating (t2) description with values from (t1) based on item code

I've tried vlookup but the result is wrong....

 

[pixel]

I have 2 tables

- 1st table (t1) contains 2 columns, item code and description, item code is unique
- 2nd table (t2) also has 2 columns as t1 - it has multiple non unique item codes but description is blank

For each row in (t2) I need to first match the item code from (t2) with item code from (t1) then fill (t2) description with the (t1) description

essentially updating (t2) description with values from (t1) based on item code

I've tried vlookup but the result is wrong....

check that t1 is sorted by code ascending.
otherwise, vlookup won't work.

 

[Uncle Festivus]

check that t1 is sorted by code ascending.
otherwise, vlookup won't work.

Thanks Pixel. Did that and changed absolute reference - =VLOOKUP(C2,t1!$A$1:$B$479,2,FALSE)

 

[pixel]

Thanks Pixel. Did that and changed absolute reference - =VLOOKUP(C2,t1!$A$1:$B$479,2,FALSE)

I like to use names as reference; if for no other reason than documentation.
So, the table in t1 could be called Code.Lookup or Code.DB; then I use that name in the VLOOKUP in t2.
Additional benefit: Whenever I have to add a code, I just insert a row, and the name is automatically updated.

 

[VSntchr]

Hello excel guru's, I am in need of some help!

I have a formula which I use to find the SUMPRODUCT of of two ranges, however I would like to know if it is possible to employ this formula with a condition that it auto updates as I fill in the ranges to show only the LAST 30 DAYS.

I.e. SUMPRODUCT of the ranges for last 30 days.

Thanks.

 

[pixel]

Hello excel guru's, I am in need of some help!

I have a formula which I use to find the SUMPRODUCT of of two ranges, however I would like to know if it is possible to employ this formula with a condition that it auto updates as I fill in the ranges to show only the LAST 30 DAYS.

I.e. SUMPRODUCT of the ranges for last 30 days.

Thanks.

Hi VS;
Have you tried comparing the date column to "today()-30"?

e.g. {=sum(if(daterange>today()-30,valuerange,0))}

Attach an example and I'll amend your formula.

 

[hhse]

=A$1/A2

Where A, B, C , D etc is aloong top

 

[VSntchr]

Hi VS;
Have you tried comparing the date column to "today()-30"?

e.g. {=sum(if(daterange>today()-30,valuerange,0))}

Attach an example and I'll amend your formula.

Hi Pixel, thanks for the reply.

So I think what your suggesting will, work I am just having a bit of trouble implementing.

My formula for the Total calculation is: =SUMPRODUCT(F21:F69,E21:E69)+SUMPRODUCT(F21:F69,L21:L69)+SUMPRODUCT(J21:J69,I21:I69)+SUMPRODUCT(J21:J69,M21:M69)


The formula in question must do the same thing, but only for the last 30 days from todays date...given the date range of K21:K69

 

[pixel]

Hi Pixel, thanks for the reply.

So I think what your suggesting will, work I am just having a bit of trouble implementing.

My formula for the Total calculation is: =SUMPRODUCT(F21:F69,E21:E69)+SUMPRODUCT(F21:F69,L21:L69)+SUMPRODUCT(J21:J69,I21:I69)+SUMPRODUCT(J21:J69,M21:M69)


The formula in question must do the same thing, but only for the last 30 days from todays date...given the date range of K21:K69

I interpret what you're writing like this:
In column K you have the dates
Then you want to add up F*E + F*L + J*I + J*M ... is that correct?
If so, I wouldn't use all those SUMPRODUCT terms, but use the conditional SUM(IF ... as I mentioned earlier. You could insert the date condition IF(K21:K69>TODAY()-30 in each of your sumproduct terms, but that would make the formula unwieldy and almost impossible to understand.

Use F*(E+L) + J*(I+M) on condition that K>today()-30
Remember to use only >30, not >=30, because today()-today's date will be zero, and you therefore want to use the 30 days from 0 to 29.

I always prefer naming my data ranges; as I don't know what types data you're talking about, I can't suggest any meaningful names, but I'm sure between this reply and my earlier one, you'll get the drift.

If you keep adding rows below row 69, you can define the range down as far as seems useful because the conditional date selection won't cut in for rows where column K is empty (=0).

 

[skc]

Hi Pixel, thanks for the reply.

So I think what your suggesting will, work I am just having a bit of trouble implementing.

My formula for the Total calculation is: =SUMPRODUCT(F21:F69,E21:E69)+SUMPRODUCT(F21:F69,L21:L69)+SUMPRODUCT(J21:J69,I21:I69)+SUMPRODUCT(J21:J69,M21:M69)

The formula in question must do the same thing, but only for the last 30 days from todays date...given the date range of K21:K69

With small amount of data it's often easier to add additional columns as intermediate calculation step. For example, you can add a column that "date within 30 days" and simply has it as 1 or 0. The formula would be something like =IF(daterange>today()-30,1,0) and just fill it down. You can then sumproduct this column in addition to your other data, and you will get just the sumproduct for the last 30 days.

Having additional columns is often the dumb way of doing things but it allows better detection of gross error, as you can check the calculation at each step. You can always hide these intermediate step columns for display purpose as well.

Something else to think about:

Do you want to sum the last 30 rows? Or do you want to sum today's date - 30 which may have anywhere between 22-25 entries (depending on weekend etc)? Or do you actually wanted to sum calendar month's data?

 

[VSntchr]

SUMIF(F21:F69*(E21:E69+L21:L69)+J21:J69*(I21:I69+M21:M69),K21:K69>today()-30))

I tried the above but I have an error...I know that what your saying is correct, I am just not implementing it correctly.

 

[VSntchr]

With small amount of data it's often easier to add additional columns as intermediate calculation step. For example, you can add a column that "date within 30 days" and simply has it as 1 or 0. The formula would be something like =IF(daterange>today()-30,1,0) and just fill it down. You can then sumproduct this column in addition to your other data, and you will get just the sumproduct for the last 30 days.

Having additional columns is often the dumb way of doing things but it allows better detection of gross error, as you can check the calculation at each step. You can always hide these intermediate step columns for display purpose as well.

Something else to think about:

Do you want to sum the last 30 rows? Or do you want to sum today's date - 30 which may have anywhere between 22-25 entries (depending on weekend etc)? Or do you actually wanted to sum calendar month's data?

I actually thought about your first suggestion SKC, and have used that method with some of my other metrics in the spreadsheet. If I can't follow Pixels instructions I will revert to that.

And what I am trying to achieve is total turnover for last 30 days, so that I get a rolling 30 days figure...my provider gives a rebate over a certain transaction level per month so I want to see when I am hitting that level so I can demand the money!

 

[skc]

I actually thought about your first suggestion SKC, and have used that method with some of my other metrics in the spreadsheet. If I can't follow Pixels instructions I will revert to that.

And what I am trying to achieve is total turnover for last 30 days, so that I get a rolling 30 days figure...my provider gives a rebate over a certain transaction level per month so I want to see when I am hitting that level so I can demand the money!

If you just want rolling 30 day, you simply have a column that's sumproduct of the previous 30 rows... and fill down on that.

So no formula for rows 1-29. On row 30 you have =sumproduct(A1:A30,B1:B30). On row 31 you have =sumpoduct(A2:A31,B2:B31). It's basically calculating a simple moving total... You don't even need to know the date!