PDA

View Full Version : Solved: Feeling dumb today!!!



marshybid
06-18-2008, 05:27 AM
OK, I need to write a formula to then populate down a column of cells, please help;

Column A = Order date (if no order then A=<Null>)
Column B = Order confirmation date (if no order then B=<Null>)

This is essentially the data that I start with.

I would like a formula to do the following;

C1=IF(A1<>"<Null>",IF(B1 between today and today -7 THEN "1", " ")

I think I just left my brain at home in bed today!!!

Thanks,

Marshybid

tinyjack
06-18-2008, 05:29 AM
"" is what you could use for "<Null>" & TODAY() might be of help.

HTH

TJ

RichardSchollar
06-18-2008, 05:32 AM
Hi

=IF(ISNUMBER(A1)*(B1>TODAY()-7)*(B1<=TODAY()),1,"")

You may want to amend the > and <= operators dependent on your exact requirements.

Richard

marshybid
06-18-2008, 06:13 AM
Hi

=IF(ISNUMBER(A1)*(B1>TODAY()-7)*(B1<=TODAY()),1,"")

You may want to amend the > and <= operators dependent on your exact requirements.

Richard

Richard,

Perfect solution to the problem. I must remember my brain in the morning.

I'm not familiar with the way you have written the formula though??

What does * do? It seems to serve more than one purpose.

Thanks for the help. Marking as solved

Marshybid

RichardSchollar
06-18-2008, 06:35 AM
I used the * (ie the multiplication operator) between each term of the conditional IF argument (ie the bit that results in True or False, telling Excel which value for the If to return) because it produces an AND effect:

only if the 3 terms are all True (terms being Isnumber(a1) for example as well as the two bracketed expressions using TODAY()) will the IF execute the True result (because True*True*True = 1, but anything other than 3 Trues will result in a 0 (or False)).

Make sense?

marshybid
06-18-2008, 06:48 AM
I used the * (ie the multiplication operator) between each term of the conditional IF argument (ie the bit that results in True or False, telling Excel which value for the If to return) because it produces an AND effect:

only if the 3 terms are all True (terms being Isnumber(a1) for example as well as the two bracketed expressions using TODAY()) will the IF execute the True result (because True*True*True = 1, but anything other than 3 Trues will result in a 0 (or False)).

Make sense?

Makes perfect sense now.

I had just never seen a formula written using the * in that way. Now I have I will put it to good use.

Once again VBA Express wins the day.

Thanks loads.

Marshybid :hi:

Bob Phillips
06-18-2008, 07:06 AM
I used the * (ie the multiplication operator) between each term of the conditional IF argument (ie the bit that results in True or False, telling Excel which value for the If to return) because it produces an AND effect:

only if the 3 terms are all True (terms being Isnumber(a1) for example as well as the two bracketed expressions using TODAY()) will the IF execute the True result (because True*True*True = 1, but anything other than 3 Trues will result in a 0 (or False)).

Make sense?

OK, so why didn't you use a bog-standard AND(condition,condition,condition) - * being used to emulate AND should only be used when AND fails - IMO.

marshybid
06-18-2008, 07:11 AM
OK, so why didn't you use a bog-standard AND(condition,condition,condition) - * being used to emulate AND should only be used when AND fails - IMO.

Hi xld,

Is there a reason why AND would fail? Also, how would the use of * fix this problem?

This was why I went back to Richard initially as I had never seen * being used in this way.

Thanks,

Marshybid :rotlaugh:

Bob Phillips
06-18-2008, 07:17 AM
AND does not fail in your situation.

It will fail if you try to AND an array, because AND does not return an array, just a single value, then you use *.

marshybid
06-18-2008, 07:21 AM
As ever, learning something new every day.

Thanks for the clarification

Marshybid

RichardSchollar
06-18-2008, 08:01 AM
OK, so why didn't you use a bog-standard AND(condition,condition,condition) - * being used to emulate AND should only be used when AND fails - IMO.

It was just the way the formula flowed thru my fingers!

By the way Bob, your site appears to be down - is it only temporary or more long term?

EDIT: Scratch that - it ain't down anymore...

Bob Phillips
06-18-2008, 08:15 AM
By the way Bob, your site appears to be down - is it only temporary or more long term?

EDIT: Scratch that - it ain't down anymore...

I felt that statement coming through the ether, so I upped and fixed it!

RichardSchollar
06-18-2008, 08:30 AM
:):):)

I guess we'll have to add another shot of the Sapphire to my round ;)

Bob Phillips
06-18-2008, 08:38 AM
No, I think I owe you that one!