Formula help!!

  • Thread starter Thread starter mx-3 for me
  • Start date Start date
M

mx-3 for me

Hello all, well I wrote once yesterday and got a good answer but
realized I wasnt wording myseld correctly. So Im going to tr
again...
I was wondering if there was any possible way for me to have a formul
that I can fill throughout the 8,000 cells that I need to fill throug
that has the following conditions:
One of the cell references needs to be incremented per cell, the othe
cell reference needs to stay static for 24 values but after the 24t
value, that cell reference needs to be incremented by 1, and then sta
static again for another 24 cells, and then increment by 1- and the
static...and so on and so fourth until the 8,760th value. So as yo
can see, it isnt something I want to do manually- Does anyone know if
can do this? and if so how?
PLEEEEEEEASE help!! Im at my wits end about this issue:confused:

Thanks in advanced!
Michell
 
It would help if you gave us some manual examples of the formula you would like
in say 4 or 5 cells and how you would see it increment in each case - Plus tell
us which of the references hasn't incremented, but is to do so in24 cells time.
 
Hi

In my example the first range start from cell A2, and start value will be 1.
=ROW(A2)-ROW($A$2)+1
The second range starts from cell B2, and start value will also be 1
=INT((ROW(B2)-ROW($B$2))/24)+1

Copy both cells down for 8760 rows.

When you want start from some other number, then replace the number 1 in
formulas with this number.
Really formulas given as example aren't bound to cells A2:B2 - you can write
exactly same formula into any cell, and then copy it down. But so you may
encounter problems, when you delete some columns or rows afterwards. So my
advice is, in starting formula refer always to same cell where the formula
is. And never delete the uppermost row of range (the one the absolute
reference is pointing to).
 
Beware: this formula is nasty...
=IF(OR((C22/100)<HOUR(Sheet1!D$6),(C22/100)>HOUR(Sheet1!E$6)),Sheet1!H$6,IF(AND((C22/100)>HOUR(Sheet1!D$6),(C22/100)<HOUR(Sheet1!E$6)),0,IF((C22/100)=HOUR(Sheet1!D$6),(MINUTE(Sheet1!D$6)/60)*Sheet1!H$6,((60-MINUTE(Sheet1!D$6))/60)*Sheet1!H$6)))

This is the formula I want to use, I want the H and D cells to onl
increment by 1 every 24th cell down... its referencing to 24 hours.
The C values can increment all they want- its just the D and H value
im having problems with. I just want to be able to fill all the wa
down to the close to 9,000 cells that need to use this crazy formul
without having to alter the formula every 24th cell, because id the
have to alter it 365 times per sheet I want to do this to.
Is there any way around this? My boss tagged this issue on me becaus
he cant figure it out, and im a student!
:rolleyes: Need the help of Gurus such as yourselves! Thanks sooooooo
much
 
Hi

At start a somewhat shorter version for your formula

=OR(C22/100<HOUR(Sheet1!D$6);C22/100>HOUR(Sheet1!E$6))*Sheet1!H$6+(C22/100=H
OUR(Sheet1!D$6))*(MINUTE(Sheet1!D$6)/60)*Sheet1!H$6+(C22/100=HOUR(Sheet1!E$6
))*((60-MINUTE(Sheet1!D$6))/60)*Sheet1!H$6

A comment: the formula sums 3 values (=a+b+c, you don't need to sum the case
returning 0). There are never any 2 values with conditonal parts being TRUE
simultanously. So the formula returns eithe a or b or c or 0

As for cell referance incrementing, I'm not sure what do you want to do
exactly. But I have a feeling you need OFFSET function. P..e when you want
the reference to cell in column D on sheet1 to be incremented only after in
column you move down for 24 rows, then the reference will be (on fly)

HOUR(Sheet1!D$6) -> HOUR(OFFSET(Sheet1",INT((ROW(C22)-ROW(C$22))/24),0))
 
Yes Arvi, that is exactly what I need to do, however I am unsure how t
use the OFFSET function... how would I rewrite my entire formula s
that every D6,H6 and E6 does what I want it to do? (which is what yo
said in your last post about using OFFSET to increment every 24t
cell...)

=IF(OR((C3/100)<HOUR(Sheet1!D$6),(C3/100)>HOUR(Sheet1!E$6)),Sheet1!H$6,IF(AND((C3/100)>HOUR(Sheet1!D$6),(C3/100)<HOUR(Sheet1!E$6)),0,IF((C3/100)=HOUR(Sheet1!D$6),(MINUTE(Sheet1!D$6)/60)*Sheet1!H$6,((60-MINUTE(Sheet1!D$6))/60)*Sheet1!H$6)))


Thank you so much for your help- you have been a God send, I just nee
that extra little push to get past this roadblock...

TI
 
Hi

=IF(OR((C3/100)<HOUR(OFFSET(Sheet1!D$6,INT((ROW(C3)-ROW(C$3))/24),0)),(C3/10
0)>HOUR(OFFSET(Sheet1!E$6,INT((ROW(C3)-ROW(C$3))/24),0))),OFFSET(Sheet1!H$6,
INT((ROW(C3)-ROW(C$3))/24),0),IF(AND((C3/100)>HOUR(OFFSET(Sheet1!D$6,INT((RO
W(C3)-ROW(C$3))/24),0)),(C3/100)<HOUR(OFFSET(Sheet1!E$6,INT((ROW(C3)-ROW(C$3
))/24),0))),0,IF((C3/100)=HOUR(OFFSET(Sheet1!D$6,INT((ROW(C3)-ROW(C$3))/24),
0)),(MINUTE(OFFSET(Sheet1!D$6,INT((ROW(C3)-ROW(C$3))/24),0))/60)*OFFSET(Shee
t1!H$6,INT((ROW(C3)-ROW(C$3))/24),0),((60-MINUTE(OFFSET(Sheet1!D$6,INT((ROW(
C3)-ROW(C$3))/24),0)))/60)*OFFSET(Sheet1!H$6,INT((ROW(C3)-ROW(C$3))/24),0)))
)

I hope I didn't do any mistakes there. But anyway, I think you got the idea

The same using the modified formula from my previous post

=OR(C3/100<HOUR(OFFSET(Sheet1!D$6,INT((ROW(C3)-ROW(C$3))/24),0));C3/100>HOUR
(OFFSET(Sheet1!E$6,INT((ROW(C3)-ROW(C$3))/24),0)))*OFFSET(Sheet1!H$6,INT((RO
W(C3)-ROW(C$3))/24),0)+(C3/100=HOUR(OFFSET(Sheet1!D$6,INT((ROW(C3)-ROW(C$3))
/24),0)))*(MINUTE(OFFSET(Sheet1!D$6,INT((ROW(C3)-ROW(C$3))/24),0))/60)*OFFSE
T(Sheet1!H$6,INT((ROW(C3)-ROW(C$3))/24),0)+(C3/100=HOUR(OFFSET(Sheet1!E$6,IN
T((ROW(C3)-ROW(C$3))/24),0)))*((60-MINUTE(OFFSET(Sheet1!D$6,INT((ROW(C3)-ROW
(C$3))/24),0)))/60)*OFFSET(Sheet1!H$6,INT((ROW(C3)-ROW(C$3))/24),0)

Check both formulas over - they both must return same result.

Some further advice. Create 3 named ranges (select the cell you used to
enter you formula into - the one with references to C3, Sheet1!D$6,
Sheet1!E$6 and Sheet1!H$6 - before!). To create a named range, select from
menu Insert.Name.Define, enter the range name, and into 'Refers to' field
enter the formula.

A=OR(C3/100<HOUR(OFFSET(Sheet1!D$6,INT((ROW(C3)-ROW(C$3))/24),0));C3/100>HOU
R(OFFSET(Sheet1!E$6,INT((ROW(C3)-ROW
(C$3))/24),0)))*OFFSET(Sheet1!H$6,INT((ROW(C3)-ROW(C$3))/24),0)

B=(C3/100=HOUR(OFFSET(Sheet1!D$6,INT((ROW(C3)-ROW(C$3))/24),0)))*(MINUTE(OFF
SET(Sheet1!D$6,INT((ROW(C3)-ROW(C$3))/24),0))/60)*OFFSET(Sheet1!H$6,INT((ROW
(C3)-ROW(C$3))/24),0)

C=(C3/100=HOUR(OFFSET(Sheet1!E$6,INT((ROW(C3)-ROW(C$3))/24),0)))*((60-MINUTE
(OFFSET(Sheet1!D$6,INT((ROW(C3)-ROW(C$3))/24),0)))/60)*OFFSET(Sheet1!H$6,INT
((ROW(C3)-ROW(C$3))/24),0)

Instead A, B and C you can enter any valid (i.e. Excel himself doesn't use
it - usually it complains when it does) name you like, of-course.
Now you can have your formula in much simpler form
=A+B+C
Copy it down
 
It says there is some kind of error in the formula when I tried thos
options- all of them. Even defining and named range. hmmm.:confused:
The C values are supposed to increment as normal, its just the D E an
H values that are only to increment every 24th row.

I cannot thank you enough for your time and effor
 
Hi

Checked it, there was an error in formula for named range A - a semicolon
was not replaced with comma.

When you copied the formula from message into command bar, then did you
remove all hard return's and/or abundant spaces? When not, then you get
error!
To check, copy p.e. formulas for name ranges into 3 different cells on same
row, and edit them to remove spaces and new row codes (hard returns). When
done properly, the formula returns a meaningful result (p.e. with empty
workbook, on sheet2, all formulas return 0). When you are on same row with
cell in column C, you are referring to (Sheet2!3:3 ?), then you can define
named ranges from there, copying the tested formula into 'Refers to' field
 
Oh my god, you are AMAZING!!
Thank you so much for your help, you have no idea how great this is!

Michell
 
Back
Top