Be Excellent To Each Other

I need help with a forumla for my gaming spreadsheet.

I have column H which shows the price I paid for each game. Column I shows the date I bought that game.

I would like a cell that adds up how much I spent in 2014, another cell for 2013 and so on.

Any ideas?

Add a column (hide it if you want) using the formula YEARS(whateveryourdatecolumnis) which will give you the year - then a pivot on year + cost

So your data ends up looking like this



And the pivot gives you

That looks confusing and indeed, it is when I've tried to do it. I was hoping just to have one cell with 2014's amount in, another with 2013 and so on.

You can of course hide all of this on another sheet and make the main one look simple but the other way would be to just sort by year and then have a total for the year in a separate line.

I'm assuming you never go back and add in stuff from a previous year so once done they are 'set' ?

I will get to that point but at the moment no. I have found the odd receipt here and there which I've added on.

Then just having a total column at the end and having it sum all the ones up to that point would work fine - if you add a new line in for something old you've found you just re-sort and it will be included - it relies a lot more on you doing the work than the pivot but its at least simple

Create the years column as column I, hide it, then do something like this



where I is your year column, and H is your total column.

Bingo! That's done it. Thanks both for your help.

I've got a great spreadsheet that does VLOOKUPS from about 6 other sheets saved on the network. On my laptop, it works fine. But, it never updates on my boss's when he opens it. Is there a reason for this?

Maybe he has his network drives mapped differently? I think it would report the error somewhere though if it can't find one of the files it needs to perform the lookup though.

Do the lookups refer to a unc path, or the drive letter? If the latter, check it's mapped to the same letter.

Also, in the trust centre, check that the box for "allow trusted network locations" is ticked and the network drive is listed as trusted. If not, you may end up with a load of REF! In your spreadsheet.

Zaphod's answer is the 'cleanest' IMO.

Since learning to use pivot tables and the 'grouping' ability within pivot tables, I have felt like the excel boss at work. You should seriously learn to use pivot tables, that will put you ahead of easily 90% of people that list 'MS Office' on their CV

Although to be fair, when it comes to 1337 IT skillage, the bar is quite low at this place, so if I wasn't the 'best at computers' here it would be utter shame upon me.

You appear to be answering a three year old question.

lolz, I was just so excited about having learned something new that actually helped me to do some stuff at work heaps faster (trawling through month worth of QC data)

Although, I was more commenting on Zaphod's reply, so nerrrr

Very much this. People look on in awe when I use pivots, and I quietly think back to how when I was first shown them, I too thought they were witchcraft. One day I'll level up and master INDEX MATCH MATCH.

Not specifically Excel help, but related.

I am creating a Mail Merge in Word (people still do those, right?)

Each mail will have a unique link for users to click. This is being pulled from an Excel document.

Question: How do I merge that link under display text i.e. I can pull in the link using Mail Merge, but cannot work out how to change the display text e.g. click here.

At present it's just merging the url as is.

The person who helps will be the proud recipient of a mail merged message from yours truly.

This StackOverflow post might help?
https://stackoverflow.com/questions/156 ... -same-text

What happens if you do the 'click here' type stuff in Excel first? i.e., using =HYPERLINK(A1,"Click Here") where A1 holds your URL and then bringing that new field through instead?

Thanks folks. Excel isn't accepting either of these and I can't work out why:

=hyperlink(http://www.gmail.com,)
=hyperlink(http://www.gmail.com,[Click_Here])

I hate Excel.

The URL and display text will need quotes around them.

boom! Thank you

If you stick the URL in one cell, the text in another (exactly the same format as above) then do =hyperlink(A1,B1) it works on mine but actually typing it in doesn't.

Stupid Excel

That's even better! Thank you.

Okay, need help. Or rather elegance.
I have two time variables as columns, eg:
start time: - 14:30
end time: - 17:30

and another set of times and costs:


I need to assign the difference between start and end times pro rata against the relevant cost level, ie

17:30 - 14:30 = 3 hours.
2.5 hours @ 83
0.5 @ 95

No, I could spend ages doing this through various if statements, but I wondered if there were a more elegant way.

I would get the number of minutes between the two times:

And then multiply by the cost per minute:


If you need to round up or down to hours then just divide by 60 rather than 1440 and use =roundup() or =rounddown(). Then leave C1 as it is.

but I need a way of allocating the minutes, or am I missing something?

This page looks to be trying to solve the same sort of problem with the banding of pay rates and calculating how much of each rate falls into each band:

http://www.meadinkent.co.uk/xl_payenh.htm

the problem I am having is that the shifts can start at any point and span multiple cost boundaries.

thanks though.

I may have to give up.

I've got to say, I'm not really sure what you're trying to do

Are there dates, too? Some of those times have a later start then end, suggesting they've rolled over midnight

Oh, that's not such a hardship. Just do an if to see if one is bigger than the other and take the evening start hour away from the morning end hour plus 24.

Except it could be 8 days later, there's no way to tell

Valid.

I've just actually realised what Bobby is fuckering on about. Those time slots aren't the start and end times, they're rates. So if you work three hours between 7pm and 7am, the rate is X. I'll have another look when all the Zeds have been dealt with.

Yes, there are three time slots, in the list, these assign a cost/hour to any work done. The work done can span these time slots and needs to be divided accordingly. So if I start work at 16:50 and finish at 17:20 I have 10 minutes at rate 1 and 20 at rate 2.

It is unlikely they would span more than 3 shifts, though.

Why are there two rows for 22:00 to 7:59:59?

We'll, that's the next step, that is the weekend rate! Ignore that for now.

Elegant this is not.
Assuming the stuff you posted on the previous page is in cells ("A1:C3"), put your start and end times in D1 and E1, then put this monstrosity into E1 and copy down for each shift range (i.e. Rows 1-3). This should give you the total rate for each range (I assumed it was an hourly rate, and rounded to the nearest 15 minutes).
=(ROUND(MINUTE(IF((IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1)))<0, ((IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))+1)-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1))), (IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1)))))/15, 0)/4*C1)+(HOUR(IF((IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1)))<0, ((IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))+1)-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1))), (IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(E$1>B1, B1, E$1)))-(IF(OR(AND(D$1<A1, E$1>B1, B1<A1), AND(D$1>A1, E$1<B1, B1>A1)), 0, IF(D$1>A1, D$1, A1)))))*C1)

Actually, ignore that, it doesn't work if you span multiple shifts and days, 14.30 - 17.30 works, 23.00 - 5.00 works, but 21.00 - 05.00 doesn't.

Does this have to be done in Excel?

It looks amazing though, Joans

I'd be thinking something other than excel would be preferable for this. Or VBA sitting behind excel would certainly do it. I think maybe one approach would be to split the time you worked into 30-minute chunks, and for each chunk check which bracket it's in and apply (half) the appropriate rate.

Would this work?

Create a static table, start and end times along x and y, and fill in the calculated values. Then use index() and match () to look up from the table.

Wouldn't you spend about the next 15 years working out what all the calculated values would need to be to fill in the table?

Yeah even VBA would be helpful, however I work with what I've got. So, try this...

=(ROUND(MINUTE((IF((IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1))))<0, ((IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))+1)-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1)))), (IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1)))))))/15, 0)/4*C1)+(HOUR((IF((IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1))))<0, ((IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))+1)-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1)))), (IF(AND(A1<B1, D$1>B1), 0, IF(AND(D$1>B1, E$1<A1, D$1<E$1), 0, IF(OR(AND(E$1<B1, F$1>C1, C1<B1, E$1<F$1), AND(E$1>B1, F$1<C1, C1>B1, E$1<F$1)), 0, IF(D$1<E$1, IF(E$1>B1, B1, E$1), IF(D$1<B1, B1, E$1))))))-(IF(AND(A1<B1, D$1>B1), 0, IF(OR(AND(D$1<A1, E$1>B1, B1<A1, D$1<E$1), AND(D$1>A1, E$1<B1, B1>A1, D$1<E$1)), 0, IF(D$1>A1, D$1, A1)))))))*C1)

And people say Perl is unreadable.

I'm sure a formula could be worked out to do most of it.

In fairness it's because I had about 7 calculated fields, which I combined into one user-friendly formula. If I did it all separately it wouldn't be that bad, but where's the fun in that?

Wow! Joans, am of work today, week try it tomorrow, but seriously, thanks for the effort!

It still doesn't quite work, sorry, something like 21:00 - 09:00 sends it a bit funny.