IF Functions

[caty]

I'm trying to organise a media plan using IF functions
with the date.
For example, if cell value=>0, then reference the date
above it, as the date of start of activity, or false, +0.
If value is false, then reference the date on the cell to
the right (which increases by week).
however, if that cell is also false, how can i have an IF
function saying - move until you find a true value? i
have tried putting more than one IF function in the cell
and can't get it to work.

any help would be appreciated!

 

[Frank Kabel]

Hi
not really sure what you're trying to do but probably
HLOOKUP would work for you. Could you post some example
data (plain text - no attachment please)

 

[Guest]

ok - i'll try to be a bit more specific!

date by week runs along the top of the table, and at the
end of the time period is 2 columns - one is start date,
one is end date.
rows are media activity - lets say TV spend.

If there is no media activity in the first week, i would
like the start date to reference the next week's cell,
and ongoing until it finds a value (which it will.)

I have used a simple IF function already, but it only
takes me to the second week. After that, if activity was
to start in the 3rd or 4th weeks, i'm not sure how to get
it to continue to reference... hopefully that makes a bit
more sense?

thanks for your help

 

[Frank Kabel]

Hi
so if your dates are in A1:X1 and the media activity in
A2:X2 use the following formula entered as array formula
with CTRL+SHIFT+ENTER):

 

[Guest]

That's great - excellent - it works for the Start date
but if I want to work the End date backwards - so from
X1:A1 looking up the date from the end, is that possible?

 

[Guest]

This formula is still referencing cells with a formula,
even though the formula=0 ? it only is adjusted once the
formula is deleted. Is there a way around this as well!?!

 

[Frank Kabel]

Hi
what does the formula return? a zero '0'. If yes try the
following array formula
=INDEX(A1:X1,MATCH(TRUE,(A2:X2<>"")*(A2:X2<>0),0))

To get the last filled value in this range use the
followingb array formula