Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula question
I need help with getting a formula to work.
I have a spreadsheet that add time accured and subtracts time used... what I need is for it to stop and not continue to add accured time if the max is reached. Thanks for any help offered. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula question
If the accurals are in column A and the used are in column B:
=MIN(SUM(A1:A100)-SUM(B1:B100),1234) where 1234 is the max -- Gary''s Student - gsnu200909 "Roberta" wrote: I need help with getting a formula to work. I have a spreadsheet that add time accured and subtracts time used... what I need is for it to stop and not continue to add accured time if the max is reached. Thanks for any help offered. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Formula question
"Roberta" wrote:
I have a spreadsheet that add time accured and subtracts time used. what I need is for it to stop and not continue to add accured time if the max is reached. A concrete numerical example would help resolve ambiguities in the English description. Perhaps one of the following will work for you: =min(--"12:30", sum(A1:A10)) - sum(B1:B10) =min(--"12:30", sum(A1:A10) - sum(B1:B10)) In both case, the formula results in the lesser of 12h 30m and something else. The "--" has the effect of converting the string to a time value. This assumes that you have bona fide time values in A1:A10 (time accrued) and B1:B10 (time used), not text. If you (temporarily) format the cells as hh:mm, you should see the times that you expect. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Question | Excel Discussion (Misc queries) | |||
Formula Question | Excel Discussion (Misc queries) | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
Formula Question | Excel Discussion (Misc queries) | |||
formula question | Excel Worksheet Functions |