#### Biggskip

##### Distinguished Member
Originally Posted by ginlimetonic
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.
Originally Posted by rajesh06
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.

#### rajesh06

##### Senior Member
Originally Posted by Biggskip

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

#### Kajak

##### Distinguished Member
Originally Posted by Biggskip
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.

#### Reevolving

##### Distinguished Member
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)

#### rajesh06

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

#### Reevolving

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

#### jgold47

##### Distinguished Member
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.....

#### rajesh06

##### Senior Member
I think the answer lies with the OFFSET function.

#### MetroStyles

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

#### JesterM

##### Well-Known Member
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

#### rajesh06

##### Senior Member
Look up conditIonal formatting

#### cahlersmeyer

##### Senior Member

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?

#### Bhowie

##### Stylish Dinosaur

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.

Last edited:

#### cahlersmeyer

##### Senior Member

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

## Most Interesting Fashion Collaboration of 2020

• ### Lego x Levi's

Results are only viewable after voting.