find measure prev. day qty vs bus. hrs today/8..

[nastech]

trying to find way to compare a previous days quantity, to today's business
hours, probably as a percentage of a day?... thanks

have sample of formula:

IF(TODAY()>DATE(MID(T9,2,2)+100,MID(T9,4,2),MID(T9,6,2)-(10+1)),"d",

for yymmdd / NOW
2007-04-30 8:41:43 PM

 

[Leung]

What do you put in T9?
How's your worksheet is constructed?

 

[nastech]

hi, thanks for responding, this is an old problem couldn't get many months
ago..
dates used couple of ways in this sheet (detail is from many hours work, not
super-expert, please have patience). not sure what is too much info / how to
setup formula.

I have found dates hard to figure out. where that item is example of what
using, must see what "Looking For" below, thanks

T9 used to hand enter a expiration date in the form of :yymmdd (for a
verified date), and ;yymmdd for non-verified. full formula is (for colon /
semi-colon)
=IF(OR(CU9={"",0}),"",IF(OR(BJ9="br",AND(LEN(R9)>=5,MID(R9,5,1)="q")),"br",IF(LEFT(T9,2)=":c","c",IF(AND(CU9<1,OR(LEFT(T9,1)={":",";"}),ISNUMBER(ABS(MID(T9,2,6))))
IF(TODAY()>DATE(MID(T9,2,2)+100,MID(T9,4,2),MID(T9,6,2)-(10+1)),"d",IF(LEFT(T9,1)=";","-",IF(RIGHT(CQ9,1)="x","z",""))),IF(RIGHT(CQ9,1)="x","z",IF(CU9<1,"?",""))))))

LOOKING FOR: separate volume consideration, have previous days volume & get
skewed: "not enough" volume if not comparing to end-of day again. resources
is a major concern /size of file,
- need a formula smallest possible to divide portions of day I select,
e.g.: from 9:30 am to 4pm.. or sectionally less than whole hours: 10, 11,
12, 1, 2, 3, 4;
- if helps, since I download figures, maybe one fixed / absolute cell for
time data downloaded, & all cells in a column reference that cell, would lend
to using only rough times as above. 10, 11...

Formula using currently for volume valid (0), is
=IF(CU9="","",IF(CK9="",1,IF(OR(F9="x",AND($CK$4=0,CK9>$C$7),AND($C$1=0,COUNTIF($CJ9:$CL9,">="&$C$7)>=$CK$4),AND($BB$4="x",CL9>$C$8,IF(CB9="",FALSE,CB9>$CT$4))),0,1)))

Last Volume is in column CK, previous close to left: CJ, 3Mo average to
right: CL
$CK$4 enters a 1 (1-3) for minimum circumstances met.
CB is a total volume available, CC is a estimated volume available using
~.3 / 30%

If get started with something here, will guesse looking for something that
divides specified hours of a day by ~6 or 7 (10am to 4pm). thanks.

------------------------------

 

[Leung]

Hi

It's hard to figure out what you want to do by just reading your text.

It will be good if you could send a copy of your work so I can analyze for
you.

(e-mail address removed)

 

[nastech]

hi, will work on that, have a very large file. (will take some time to get
to / has too much info)
question would still be needed to be stated accurately here. working on
that..:
too detailed a goal, hard to ask correctly?:

I have an EOD (end of previous day) quantity/ volume, & a 3month average can
go by.
as work progresses the next day during business hours, volume resets to zero
(0) / starts over, during business hours: 9:30 - 4pm; Can do rough
breakdown by just dividing that period by hours, top of each hour), and maybe
compare to a fixed cell where the last download was made for time using NOW().

(not accurate example): IF reference volume (prev. eod) is 10,000
IF it is on or before 9am with a quantity of 1,000, considering 10 periods
would be at equal quantity, or 100%.
(we only need for: 9 10 11 12 1 2 3pm, or possibly 10 12 2pm for 30%
divisions)

I think I gave too much info not exactly related in other posts.. just
don't know where to start on this. thanks.

-------------------------

 

[Leung]

Hi

Although I cannot know exactly how to help you before reading your working,
I would like to point out something related to date/time.

Excel store dates and time into a number, e.g. today 4/5/07, that is 39206,
39206 is the value actually stored and used for manipulation and calculation.
for Time, it is just divided the 1 into 24 hours x 60 minues x 60 seconds,
e.g. now is 5/4/2007 10:37 but actauly stored 29206.l44276 the .44276 is
exactly the time passed from the start of the day 00:00 am.

so if you know this, it will not be difficult to do some programming by
setting some limites for time and processing your data.

 

[nastech]

hi, been awhile, but think found the answer with the following:

Have been trying to find the acceptable value of a quantity, at any given
time of day, compared to a set total quantity.

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)

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

does this appear to be correct? thanks

xxxxxxxxxxxxxxxxxxxx