I'm having a problem trying to figure out a formula that
subtracts numbers from the time.
In Cell G4 I have total Hours
Cell A29 total square inches
B29 has ? Price per square inch
Cell C29 has ? Total Price
How do I get my Price Per Sq. Inch based on $80.00 for
every 3600 Sq. Inches based on Total Hours?
This does not seem to have anything to do with subtraction or time.
If the price is $80 per 3600 sq inches, the price per sq inch in B29
is:
=80/3600
Then the total price in C29 is:
=ROUND(B29*A29,2)
Alternatively, you could let B29 be the price per 3600 sq inches,
namely 80. Then the total price in C29 is:
=ROUND(B29*A29/3600,2)
The use of ROUND ensures that the result is dollars-and-cents.
But note that B29 should not be rounded; it will create too large a
quantization error.
However, perhaps what you meant is: the rate is $80 per 3600 sq
inches, but a minimum of $80 per hour.
In that case, suppose F4 is the minimum rate per hour (80). Then C29
might be:
=ROUND(MAX(F4*G4,B29*A29/3600),2)
This assumes that the hours in G4 are written as a decimal number,
e.g. 3.5 for 3 1/2 hours, __not__ Excel time like 03:30.
If the latter, then C29 would be:
=ROUND(MAX(F4*G4*24,B29*A29/3600),2)
This is because Excel stores time of the form hh:mm as a fraction of a
day. So 1 hour is 1/24, 1 minute is 1/1400, and 1 second is 1/86400.
