Excel Help and Problems

[skyguy918]

Thanks. I see I need to be more specific and/or clearer. I have uploaded some files to the links below.

In this file is a sample of the raw data as I receive it. I then paste it into this template to calculate which rows are for peak and which are for off-peak with this file as the result. What I am trying to do is get the total peak usage from the last 12 months to be calculated into M6:M17 and the off-peak into N6:N17. One of the criteria of a SUMIF type formula would be that Column E would say "P" or "OP". The real issue is that the dates on the data vary depending on when it was pulled and the most recent 12 months will therefore be different in each data set.

As you will see in the file, when I pasted your formula into excel (I pasted it into the formula area, removed the {}, changed the end of the arrays to be row 20000, and hit CTRL-SHIFT-ENTER) it came up as zero. Not sure if that is because I did something wrong or I was unclear about what I was looking for.

Your sample data ends in the middle of April. So for the April total, do you want only the most recent (partial) April data, the last full April (ie the prior year's) data, or some combination of the 2?

[aygart]

Your sample data ends in the middle of April. So for the April total, do you want only the most recent (partial) April data, the last full April (ie the prior year's) data, or some combination of the 2?

I would imagine it would be easier to just do complete months and it doesn't really make much of a difference since it is modeled and not exact data anyhow. I am just trying to extract this aspect of the data from the model.
So whatever is simpler.

[skyguy918]

I would imagine it would be easier to just do complete months and it doesn't really make much of a difference since it is modeled and not exact data anyhow. I am just trying to extract this aspect of the data from the model.
So whatever is simpler.

Code: [Select]

{=SUM(IF(((MONTH($A$2:$A$20000)=$K6)*(YEAR($A$2:$A$20000)=MAX(IF(MONTH($A$2:$A$20000)=$K6,YEAR($A$2:$A$20000),0)))),F$2:F$20000,0))}Put the numbers 1-12 in Column K next to the month names, and then you can copy the formula as is to the whole table.

[aygart]

Code: [Select]

{=SUM(IF(((MONTH($A$2:$A$20000)=$K6)*(YEAR($A$2:$A$20000)=MAX(IF(MONTH($A$2:$A$20000)=$K6,YEAR($A$2:$A$20000),0)))),F$2:F$20000,0))}Put the numbers 1-12 in Column K next to the month names, and then you can copy the formula as is to the whole table.

This worked perfectly! Amazing! Thanks tons!

There is no way I would have gotten to this on my own, now I need to figure out what you did so that should be able to do it myself next time.

[skyguy918]

This worked perfectly! Amazing! Thanks tons!

There is no way I would have gotten to this on my own, now I need to figure out what you did so that should be able to do it myself next time.

The IF statement says that if a particular row in the array passes the logical test - which I'll explain in the next paragraph - then return the value from the usage column, otherwise return 0. So you get an array that's 19,999 cells, some filled with 0 and some with a usage value, and we sum that array.

The logical test works by 'multiplying' several logical tests by each other. The first parentheses creates a TRUE/FALSE array indicating whether each row is the month you're looking for. The second creates another TRUE/FALSE array indicating whether each row is the year you want (the one that contains the latest instance of the month you're looking for) - which I'll explain in the next paragraph. Multiplying the 2 arrays together results in 1 array that has TRUE only if both of the 'input' arrays are TRUE, and FALSE otherwise.

The year you want is determined by creating an array that's filled with 0 when the month is not the month you're looking for, and with the year where it is the right month. Take the max of that array, and you get the year that contains the latest instance of the month you're looking for.

[churnbabychurn]

Cool

[aygart]

The IF statement says that if a particular row in the array passes the logical test - which I'll explain in the next paragraph - then return the value from the usage column, otherwise return 0. So you get an array that's 19,999 cells, some filled with 0 and some with a usage value, and we sum that array.

The logical test works by 'multiplying' several logical tests by each other. The first parentheses creates a TRUE/FALSE array indicating whether each row is the month you're looking for. The second creates another TRUE/FALSE array indicating whether each row is the year you want (the one that contains the latest instance of the month you're looking for) - which I'll explain in the next paragraph. Multiplying the 2 arrays together results in 1 array that has TRUE only if both of the 'input' arrays are TRUE, and FALSE otherwise.

The year you want is determined by creating an array that's filled with 0 when the month is not the month you're looking for, and with the year where it is the right month. Take the max of that array, and you get the year that contains the latest instance of the month you're looking for.

I think I get what you did, but looking at the results more carefully now I see that April is only showing a half month's worth of usage. As I mentioned before it does not make a difference to me if it is, in this example, entirely the previous year meaning 2014, or split between 2015 and 2015. I am trying to play around with the formula but with no success yet.

[skyguy918]

I think I get what you did, but looking at the results more carefully now I see that April is only showing a half month's worth of usage. As I mentioned before it does not make a difference to me if it is, in this example, entirely the previous year meaning 2014, or split between 2015 and 2015. I am trying to play around with the formula but with no success yet.

I misunderstood, but that actually makes it easier. Will post tonight.

[skyguy918]

I think I get what you did, but looking at the results more carefully now I see that April is only showing a half month's worth of usage. As I mentioned before it does not make a difference to me if it is, in this example, entirely the previous year meaning 2014, or split between 2015 and 2015. I am trying to play around with the formula but with no success yet.

Replacement formula:

Code: [Select]

=SUM(IF((MONTH($A$2:$A$20000)=$K6)*($A$2:$A$20000>$L$5),F$2:F$20000,0)){CTRL-ENTER for array formula}
For which you should add the formula:

Code: [Select]

=EDATE(MAX($A$2:$A$20000),-12)to cell L5.

This solution sums the current fractional April with the other half of the prior April.

[aygart]

Replacement formula:

Code: [Select]

=SUM(IF((MONTH($A$2:$A$20000)=$K6)*($A$2:$A$20000>$L$5),F$2:F$20000,0)){CTRL-ENTER for array formula}
For which you should add the formula:

Code: [Select]

=EDATE(MAX($A$2:$A$20000),-12)to cell L5.

This solution sums the current fractional April with the other half of the prior April.

Beautiful! Thanks tons!

In the original formula and the first one here the * works like an AND?

[skyguy918]

Beautiful! Thanks tons!

In the original formula and the first one here the * works like an AND?

Sort of. MONTH($A$2:$A$20000)=$K6 and $A$2:$A$20000>$L$5 each create an array of TRUE/FALSE values. Multiplying TRUE/FALSE values against each other returns a 1 (which is then treated as a TRUE) or a 0 (which is then treated as a FALSE).

As I'm typing this, I realize that what I just explained means that I can just skip a step:

Code: [Select]

=SUM((MONTH($A$2:$A$20000)=$K6)*($A$2:$A$20000>$L$5)*F$2:F$20000)Because the FALSE values are 0's, they just cancel out the values in that row from the F column.

[etech0]

Weird issue over here - I wrote a simple excel macro, and assigned it to a keyboard shortcut, and it works fine. I also added the macro to the Quick Access Toolbar, but it doesn't do anything when I click it.

Any ideas? Am I losing my marbles?

[etech0]

Weird issue over here - I wrote a simple excel macro, and assigned it to a keyboard shortcut, and it works fine. I also added the macro to the Quick Access Toolbar, but it doesn't do anything when I click it.

Any ideas? Am I losing my marbles?

Update: here's the answer:

http://answers.microsoft.com/en-us/office/forum/office_2010-customize/at-startup-open-all-files-in-has-stopped-working/0a58f291-15ca-4af5-b5b8-2234e35a67d7?auth=1

Select File > Options.

Click Add-Ins.

Select Disabled Items from the Manage dropdown, then click Go...

Is the .xlsm listed? If so, select it and click Enable.

Otherwise, see http://www.jkp-ads.com/Articles/StartupProblems.asp.

[Zevi16]

Formula for time tracking.  E.g
Time in: 8.00am
Time out: 4.30pm
Total hours : 8hrs 30min.
And then times that by wage. i.e $18 per hour.
=$153.
How can I do all this simply?

[MarkS]

Formula for time tracking.  E.g
Time in: 8.00am
Time out: 4.30pm
Total hours : 8hrs 30min.
And then times that by wage. i.e $18 per hour.
=$153.
How can I do all this simply?

Assuming that everyone is working morning through  evening,  A1-8.0  B1-4.5   C1- =(12-A1)+B1    D1=C1*18

[lubaby]

Formula for time tracking.  E.g
Time in: 8.00am
Time out: 4.30pm
Total hours : 8hrs 30min.
And then times that by wage. i.e $18 per hour.
=$153.
How can I do all this simply?

Is this just calculating your own hours or for other people as well?

[mancunian]

Google 'excel convert time to decimal' - that's what you need to do, I'm pretty sure its by dividing by 60.
You also want to make sure excel knows if its am or pm, i.e. use the correct formatting.

[chucksterace]

Ok. time for a complicated formula request. (if its even possible)

I have two excel sheets with lists of products. Sheet A has the products descriptions and Sheet B doesn't. I want sheet B to compare SKU's in column B with sheet A and if they match to copy a specific cell from Sheet A to Sheet B.

Possible?

[MarkS]

Ok. time for a complicated formula request. (if its even possible)

I have two excel sheets with lists of products. Sheet A has the products descriptions and Sheet B doesn't. I want sheet B to compare SKU's in column B with sheet A and if they match to copy a specific cell from Sheet A to Sheet B.

Possible?

Wouldn't a simple V lookup do the trick?

[chucksterace]

Wouldn't a simple V lookup do the trick?

Don't know anything about that. But I can take a look.

Really quick response by the way.