The Excel Questions Thread - Page 32

I could be misinterpreting the OP's questions, but I don't think that this is what he is trying to do. You solution would create a string that would say "2minutes15seconds". I think what the OP is looking for is a way to have 2:15 and, say 4:30. Then if he wanted to had those two he would get 6:45. This mostly works except that the end result is still 12:02:15 A.M.

Agree - though with the custom format it would just display as 2:15,

nvmd

Basically. I'm trying to be lazy and get around having to figure out that 105% of a 4:00 pace is 3:49. Except for lots of things. Edit: Actually wait. apparently this works now with just treating time of day as mm:ss... weird.

In older versions of Excel, what's the best way to do subtotals in a way that still allows you to do a grand total without double counting the subtotals (since they're usually in the same column)

=subtotal(9, [range])
use the same formula for the grand total - it will ignore the intermediate subtotals

Thanks Rajesh.
This can also be done via Data->Subtotals

ok I got one.


1. Table is of 400 stores by 75 days indexed by store number
2. I want to create a daily top 5 stores table that auto refreshes based on the date I enter (or use NOW)
3. what I figured I would do was:


1. use rank to rank each store within the day
2. the vlookup 1-5 and return the store number for each day

I am stuck on how to get it to automatically update for each day....my original plan of using hlookup to return a column number based on the date wont workwithin the confines of the rank function. =rank(hlookup+cell#).


thoughs....

And then I need to get it to work within a filtered list of stores by each deal maker.....


I would be willing to use a pivot table if that would work better.....

I think the answer lies with the OFFSET function.

A pivot table would make it super easy. Just filter by day on the top and look at the top stores below.

Do any of you guys know a good formula that will change a cells color depending on the text you type in it in Excel?

For Example
If the word Open is typed into this cell, then the cell is green
If the word Closed is typed into this cell, then the cell is red

Thanks in advance

Look up conditIonal formatting

Just found this thread, so glad it exists.

I'm looking into comparing the cost of buying a Soda Stream machine vs buying soda from the store. I'm trying to figure out how many bottles I have to make in order for me to make the 79.99 machine and flavoring supplies worth it vs buying soda from the store. I've seen this excel sheet before, and did it my freshman year in college in my ISOM class, but don't know what its called? Any ideas?

lol It's a linear equation. Dude, you're in college?

[(Cost to purchase Soda @ store)*(# of Sodas consumed per year)] = [(Cost of Soda Stream $79.99)+((Cost to create each soda)* (# of Sodas consumed per year))]

Some of the variables could be slightly more complicated. Cost to create soda could be flavoring supplies and bottles, bottles would be another fixed cost. You will need to make sure the ounce cost is the same. I don't know if you buy cans, pints, or liter bottles, or if the soda stream can create various sizes that match your normally purchased size.

If the right side is less than the left side, buy the soda machine. If not, do not buy it.

This really doesn't need excel.
Edited by Bhowie - 1/3/12 at 7:27am

haha yes, I'm a journalism major... I took MATHS 108 and thats it lol