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

N

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
 
N

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.

------------------------------
 
L

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)
 
N

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.

-------------------------
 
L

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.
 
N

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top