Need help with time and liquid formula

[micko_escalade]

Hello,

I would like to use Excel and automate my calculations. Time will always vary but volume will be same
Here's one example. Every 1 minute and 17 seconds 7.48 of liquid is used. How much of liquid was used in 1 day?

Thanks in advance!

[CK76]

Try...
=1/TIME(0,1,17)*7.48
Approx. 8393.143 liquid used in a day.

[micko_escalade]

Try...
=1/TIME(0,1,17)*7.48
Approx. 8393.143 liquid used in a day.

Thanks for the reply! How can I make it so that time is pulled from A1 in this format 1:17 and in C1 I will get total?

[CK76]

How is cell formatted? mm:ss?

If so, then just =1/A1*7.48

If that's not the case, I'll need to see the actual format and what's actually stored in the cell (meaning can you upload sample?).

[micko_escalade]

How is cell formatted? mm:ss?

If so, then just =1/A1*7.48

If that's not the case, I'll need to see the actual format and what's actually stored in the cell (meaning can you upload sample?).

Yes, its in 1:17. Please see attached.

[CK76]

Ok, so it's actually in h:mm format.

In that case, you need to do adjustment like below.
=1/A2*60*7.48

Edit: Alternately...
=1/TIME(0,HOUR(A2),MINUTE(A2))*7.48

[micko_escalade]

Ok, so it's actually in h:mm format.

In that case, you need to do adjustment like below.
=1/A2*60*7.48

Edit: Alternately...
=1/TIME(0,HOUR(A2),MINUTE(A2))*7.48

Hmm... With last edit I'm getting 3:25 in C2?

[CK76]

Make sure you format the cell (C2) as number.

Otherwise, you will see it formatted as time. And time only show up to 23:59:59 and then rolls back down to 0:00:00.

[micko_escalade]

Make sure you format the cell (C2) as number.

Otherwise, you will see it formatted as time. And time only show up to 23:59:59 and then rolls back down to 0:00:00.

Thanks, that's what I was missing! My calculations are always less than 10 minutes so I should be good.

So, I've copied same formula down the column and entered 2:17 in A3 but in C3 I got 4717.313869 ? More time should equal more liquid used.

[CK76]

Huh? Your initial question was...

Every 1 minute and 17 seconds 7.48 of liquid is used. How much of liquid was used in 1 day?

If you change 1 minutes 17 seconds to 2 minutes 17 seconds... then, there will be less liquid used, since you are using same amount over longer period.

No?

[micko_escalade]

Wait a minute if I enter 2:17 in A3 is that treated as 2 minutes and 17 seconds or 2 hours and 17 minutes?

[ben_hensel]

Same volume over more time means your flow rate is slower, so over a constant time frame less liquid would be used.

[CK76]

if I enter 2:17 in A3 is that treated as 2 minutes and 17 seconds or 2 hours and 17 minutes?

Cell stores it as 2 hour 17 minutes. But the formula I gave you in post #6 converts it to 2 minutes 17 seconds.

[micko_escalade]

Huh? Your initial question was...


If you change 1 minutes 17 seconds to 2 minutes 17 seconds... then, there will be less liquid used, since you are using same amount over longer period.

No?

Never mind, I was wrong and thinking about something else

[micko_escalade]

Cell stores it as 2 hour 17 minutes. But the formula I gave you in post #6 converts it to 2 minutes 17 seconds.

Ok, If I want to enter ie. 17 seconds would I still enter it as 0:17 ?

[CK76]

That would be correct. Note that since you have it in h:mm format, you can't use value greater than 23 hours, 59 minutes (i.e. 23 minutes 59 seconds).
Otherwise you will get unexpected result.

I'd recommend using [h]:mm:ss format and entering 17 seconds as 0:00:17. And use original formula of 1/A2*7.48

[micko_escalade]

That would be correct. Note that since you have it in h:mm format, you can't use value greater than 23 hours, 59 minutes (i.e. 23 minutes 59 seconds).
Otherwise you will get unexpected result.

I'd recommend using [h]:mm:ss format and entering 17 seconds as 0:00:17. And use original formula of 1/A2*7.48

Ok, I will start using [h]:mm:ss format. Can you please explain what 1 does in this formula? I've tried manually to calculate 1/17*7.48 but I'm getting 0.44 ?

[CK76]

Excel holds datetime value as double (I.E. decimal).
1 hour = 1/24

Hence, 1 = 24 hour = 1 day

To manually calculate 1/17*60*60*24*7.48 = 38016

[micko_escalade]

Excel holds datetime value as double (I.E. decimal).
1 hour = 1/24

Hence, 1 = 24 hour = 1 day

To manually calculate 1/17*60*60*24*7.48 = 38016

Thanks for explaining that!
Based on the fact that in 0:01:17 7.48 is used how would I calculate how much is used in 1 minute and 1 hour?

[CK76]

1 min has 60 sec

So, 60/17*7.48

1 hour has 60 min

So, 60*60/17*7.48

[AliGW]

Micko - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

[micko_escalade]

1 min has 60 sec

So, 60/17*7.48

1 hour has 60 min

So, 60*60/17*7.48

Thanks!

One thing that I don't understand why Excel does this but when I do calculations in E2 I get different result in F2 compared when I
do it on my calculator 0.09 x 77 = 6.93 why is that?

[micko_escalade]

Micko - please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

For normal conversational replies, try using the QUICK REPLY box below.

Sorry, I got carried away

[CK76]

There's mistake in my post. I though you were calculating for 17 sec flow. If using 1 min 17 sec. Replace 17 in my post with 77.

At any rate I'm not sure what the calculation in E2 of your sheet represents.
=7480/86400

[micko_escalade]

That's not your mistake, I was just testing different times under and over 1 minute. Making sure calculator and Excel have same results.

Its a manual process that is done using calculator:

1440 minutes x 60 seconds = 86,400 seconds.
7480 gallons divided by 86,400 seconds = .09 gallons per second.
1 minute, 17 seconds = 77 seconds.
.09 gallons per second x 77 seconds = 6.93 gallons every 1 minute, 17 seconds.

[CK76]

Ok, that's because of rounding.

While Excel displays 0.09 due to number format (decimal place of 2).

Underlying value holds actual value (i.e. approx. 0.865740740741).

So 77*0.865740740741 is approx 6.67 (6.666...)

[micko_escalade]

You're right, I made a mistake. It should've been 7480 gallons divided by 86,400 seconds = .086 gallons per second and then the rest would have same end result as in Excel.

If I want to use per minute (60/17*7.48) and per hour (60*60/17*7.48) calculations but instead to pull time from A2 how would I do that?

[CK76]

Something like below for per Minute
=1/A2*7.48/24/60

per hour.
=1/A2*7.48/24

[micko_escalade]

Thank you!