Divide # by portion of the day??? compare to previous total column

[Guest]

(070709) Divide # by portion of the day??? compare to previous total column,
thanks

is there a formula for calculating what a portion of the day's value should
be, if you have a total quantity to start from (for numbers..), so: ??

9am - 4pm, if it is 10am, 1/7th of current count in column AA, is >= 1/7th
of col AB

 

[Sandy Mann]

With the time, (10:00am as an XL time not 10am) in say - A10 and the total
in B20 then the formula:

=(A10-"9:00")*24/7*B20

and format as General or Crrency as appropriate.

If the time in the last value in Column A and the *total* is the last value
in Column B then use:

=(LOOKUP(100^100,A:A)-"9:00")*24/7*LOOKUP(100^100,B:B)

If you want if to update at each sheet calculation the use:

=(LOOKUP(100^100,A:A)-"9:00")*24/7*LOOKUP(100^100,B:B)

=(LOOKUP(100^100,A:A)-MOD(NOW(),1))*24/7*LOOKUP(100^100,B:B)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Thankyou much.. have been asking this question since long time ago.. and
have just realized the following today. I am not that quick with these
functions, even mine; IF you could tell me if they do the same thing / in
couple words how yours works.
(I think your first example will suite me, but will keep the other in mind).

Is your formula not dependant on a stop time (i.e.: 1600 hrs..)
I am I doing the same thing with:

If the following is correct for a "relative position" equation, in percent:
=(last/from)/(to-from)*100

then trying to find the acceptable volume quantity level, for a percentage
position of the time-of-day (9:30am-4pm / 1600 hrs), might be:

=CT9>(((($DC$3-TIME(9,30,0))/(TIME(16,0,0)-TIME(9,30,0)))*100)%*CU9)

or as told, I only need:
=CT9>(($DC$3-TIME(9,30,0))/(TIME(16,0,0)-TIME(9,30,0)))*CU9

where CT9 is the Last Quantity, $DC$3 is a time stamp (with: Cntrl+Shift+;
),
and CU9 is the Total Quantity being compared to.

 

[Guest]

thanks for your answer, I have found 1 problem with my formula; where max
time is supposed to be 1600 or 4pm, if I download data after 4 pm (but last
volume occurs at 4pm), I get an inflated value because of $DC$3 time stamp
is later than 4pm. this is what I am doing:

fixed cell $L$4: =(($DC$3-TIME(9,30,0))/(TIME(16,0,0)-TIME(9,30,0)))*$M$4
and the volume column cond. format top condition (blue):

fixed cell $M$4: merely has minimum level to meet for that condition
cond. format, CT9: CT9>=$L$4

a time later than 1600 in $DC$3 inflates the minimum level?
or does your version accomodate that item. still not sure if yours stops at
1600 hours. thanks

QUESTION: Is the best answer to OR(CT9>=$L$4,CT9>=$M$4)

thanks



XXXXXXXXXX

 

[Guest]

sorry for so many replies, I'm working at it, is this the answer for that?

=((IF(NOW()<TIME(16,0,0),$DC$3,TIME(16,0,0))-TIME(9,30,0))/(TIME(16,0,0)-TIME(9,30,0)))*$M$4

 

[Guest]

Found answer, previous is wrong, but included shorter way of doing (what
correction is);

=((IF($DC$3<TIME(16,0,0),$DC$3,"16:00")-"9:30")/("16:00"-"9:30"))*$M$4

 

[Sandy Mann]

It depends on what you what to do if the download time is after 4 PM

Do you want:
1 the time in DC3 limited to 16:00 or do you want

2 the Time(16,0,0) increased to the time in DC3?

either will result in 100% after 4pm which does not seem to be helpful.

How does the volume time fingure into the calculation?

If you want you can send me a sample sheet if it helps explain what it is
that you are trying to do, just alter the address in the signature as it
says because mailinator.com is a spam trap.
--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

If you've found the answer you want then well done!

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

hi, Thankyou & thanks for the replies, appreciated; got some of what working
on from your examples. (work doing makes me look like higher speed than I
am.. putting much effort into sheet). sending sheet might not be neccessary
unless your into code. have a separate problem there. this was about the
final detail to work out before requesting code assistance, else if you have
someone to recommend.

updated shorter version of answer is: (workday stops at 1600 hours, volume
would artificially / wrongfully inflate past orginal set level with original
formula, if not checked at 1600); formula is:

=((MIN($DC$3,TIME(16,0,0))-"9:30")/("16:00"-"9:30"))*$M$4

believe it took ~ 2 years to figure that out? nuts

code working on is in one sheet, gets 200 lines max, must copy-paste results.
want to combine with main sheet. will need to include some items like a
time stamp put in $DC$3; combining this code into my sheet makes every thing
disappear upon hitting download button....

Otherswise, thanks very much.

-----

Sub GetData()

Dim QuerySheet As Worksheet
Dim DataSheet As Worksheet
Dim qurl As String
Dim i As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Set DataSheet = ActiveSheet

Range("C4").CurrentRegion.ClearContents
i = 4
qurl = "http://finance.yahoo.com/d/quotes.csv?s=" + Cells(i, 1)
i = i + 1
While Cells(i, 1) <> ""
qurl = qurl + "+" + Cells(i, 1)
i = i + 1
Wend
qurl = qurl + "&f=" + Range("A1")
'place string in cell:
Range("v1") = qurl
QueryQuote:
With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl,
Destination:=DataSheet.Range("C4"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With

Range("C4").CurrentRegion.TextToColumns
Destination:=Range("C4"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, other:=False

'turn calculation on
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
Columns("C:C").ColumnWidth = 5.14
'place cursor in cell:
Range("C4").Select

End Sub

 

[Sandy Mann]

As you don't appear to be getting any responses here I suggest that you try
the programming group microsoft.excel.programming

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk