• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Nested IF/And formula

Hi Blair ,


That was my point ; that if you want to copy + paste , or at least mimic that effect , then a simple OFFSET formula will not do.


Can you clarify a point or two ?


1. Will AG4 always be greater than 0 ? Or can it be negative too ?


2. Will your input range always be AE61:AE347 ? Or can it extend beyond AE347 ?


If your answers to both the above questions are "YES" , then what you need to do , is to enter formulae in AG61 onwards , for as many rows as you expect ; suppose the maximum value that AG4 can take is 100 , then you need to enter formulae from AG61 to AG447.


The formula itself will be taking the value in AG4 into account :


=IF(ROW()-ROW($AG$61)<$AG$4,"",OFFSET($AE$61,ROW()-ROW($AG$61)-$AG$4,))


Enter the above formula in AG61 , and copy downwards as far as you want.


Narayan
 
Cell E10 and following contains closing prices on a daily basis. Each day I will add another closing price at the bottom of Col E. I would like to duplicate all of Col E to Col AO and offset backward by the contents of AO4 (currently -26). AO4 will always be a negative number. Therefore the value of E35 would show in AO10, E36 in AO11, & etc. I’ve tried to apply the last formula you created for me but because I don’t understand the “Row()” and exactly what each element of the formula does I’m unable to apply it to the new situation. As always, I’m grateful for your help.
 
Hi Blair ,


If I understand you correctly , your data range is E10 , E11 , E12 and so on.


You would like AO10 , AO11 , AO12 and downwards to reflect the contents of column E offset by the number in AO4 , which will always be a negative number. Suppose AO4 contains -1 , then AO10 should be the same as E10 ; is this correct ?


If so , then the following formula should do the job :


=IF(ROW()-ROW($AO$10)<$AO$4,"",OFFSET($E$10,ROW()-ROW($AO$10)-$AO$4-1,))


Can you confirm whether my understanding is correct ?


Narayan
 
You must log in or register to reply here.
Back
Top