or Connect
Styleforum › Forums › General › General Chat › The Excel Questions Thread
New Posts  All Forums:Forum Nav:

The Excel Questions Thread - Page 32

post #466 of 534
Quote:
Originally Posted by ginlimetonic View Post
you can work around by extracting minutes and seconds. then use a cell reference = A2 & " minutes and " & B2 & " seconds" where A2 = cell reference for minutes, and B2 = seconds
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.
Quote:
Originally Posted by rajesh06 View Post
I don't think so via direct entry. You could convert to a serial time by using a the following formula: (m + s/60)/(24*60) and then format the cell as mm:ss (Format - Number - Custom).
This mostly works except that the end result is still 12:02:15 A.M.
post #467 of 534
Quote:
Originally Posted by Biggskip View Post



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,
post #468 of 534
nvmd
post #469 of 534
Quote:
Originally Posted by Biggskip View Post
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.
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.
post #470 of 534
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)
post #471 of 534
=subtotal(9, [range])
use the same formula for the grand total - it will ignore the intermediate subtotals
post #472 of 534
Thanks Rajesh.
This can also be done via Data->Subtotals
post #473 of 534
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.....
post #474 of 534
I think the answer lies with the OFFSET function.
post #475 of 534
A pivot table would make it super easy. Just filter by day on the top and look at the top stores below.
post #476 of 534
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
post #477 of 534
Look up conditIonal formatting
post #478 of 534
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?
post #479 of 534
Quote:
Originally Posted by cahlersmeyer View Post

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
post #480 of 534
Quote:
Originally Posted by Bhowie View Post

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

haha yes, I'm a journalism major... I took MATHS 108 and thats it lol
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: General Chat
Styleforum › Forums › General › General Chat › The Excel Questions Thread