IF formula with 3 conditions

[rvd48]

hi, i want to enhance my IF statement to include 2 more conditions.


my current IF statement has 1 condition of only checking if IBM is present.
=IF($C3="IBM", $J3, 0)


what i want to do:
(IF statement with '3' conditions). if cell C3 = IBM, and if cell F3 = 'Jul-07' or 'Aug-07', then display contents of cell J3, otherwise display 0.

How do i incorporate the other 2 'date' conditions from cell F3 into the formula?

 

[Mile-O]

=IF(AND(C3="IBM",OR(F3="Jul-07",F3="Aug-07")),J3,0)

 

[Mile-O]

=IF(AND(C3="IBM",OR(F3="Jul-07",F3="Aug-07")),J3,0)

 

[rvd48]

=IF(AND(C3="IBM",OR(F3="Jul-07",F3="Aug-07")),J3,0)

hmm, this formula seems to return back 0 when the conditions are met, its not reading cell J3. i dont get it.

 

[Mile-O]

Is your field with the date just formatted to look like Aug-07 and is actually a date or is it really a text string?

 

[rvd48]

the date field (F3) is custom formatted, mmm-yy

edit, its definatley somthing up with the date field (F3), as when i select another field instead of F3 for the OR criteria, e.g.

=IF(AND(C3="IBM",OR(E3="UK")),J3,111)

it picks up "UK" with no problem, so there is something wrong the date field, maybe the formatting?

 

[Mile-O]

Formatting doesn't affect the actual value. Therefore the formula does not see a date formatted as "Aug-07" and a date like 01/08/07 as the same thing. Therefore you'd need to look at the month and year of the date values:

=IF(AND(C3="IBM",OR(AND(MONTH(F3)=7,YEAR(F3)=2007),AND(MONTH(F3)=8,YEAR(F3=2007))),J3,0)

 

[rvd48]

Formatting doesn't affect the actual value. Therefore the formula does not see a date formatted as "Aug-07" and a date like 01/08/07 as the same thing. Therefore you'd need to look at the month and year of the date values:

=IF(AND(C3="IBM",OR(AND(MONTH(F3)=7,YEAR(F3)=2007),AND(MONTH(F3)=8,YEAR(F3=2007))),J3,0)

hmm, that formula doesnt work, it gives an error box when i enter it.

 

[rvd48]

looks like excel interprets dates as numbers, going back from the year 1900 as number 1.

Microsoft Excel stores dates as sequential numbers which are called serial values. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Excel stores times as decimal fractions because time is considered a portion of a day.

but if turn the cell F3 into the serial value, then it defeats the point of having a 'date cell' as it will just show some random 'serial' number to people

 

[Mile-O]

What was the error? It may be that I've just missed out a bracket.

 

[rvd48]

SJ, i think i have a better way of going about this formula.

my statement now is: =IF(AND(C3="IBM",OR(F3=39234)),J3,0)

Regarding Cell F3, this is a 'date' formatted cell. Excel stores dates as numbers, the number for 1st June 2007 is 39234 in excel.

what i would like to do is: IF Ibm is present in cell C3, then go to cell F3, if the date range 1st june to 31st August is there, then display whats in cell J3.

now, 1st June is 39234 as mentioned above, the serial for 31st August is 39294. so i would like the statement to say, IF the value is between 39234 and 39294, then display whats in cell J3.

how would i do this?
thankyou rvd

 

[Mile-O]

June, now?

Don't worry about the long integer value for dates. It doesn't need referred to explicitly.

=IF(AND(C3="IBM",AND(F3>=DATE(2007,6,1),F3<=DATE(2007,8,31))),J3,0)

 

[rvd48]

ok, my IF formula as it stands is:
=IF(AND($C57="2105",AND($F57>=39234,$F57<=39325)),$J57,0)

it says if the item is 2105 and the date is between 39234 and 39325, then displays whats in cell J57 otherwise return a 0.

now, i want another condition, i want it to also check if it says mp 110 as long as the current "2105". how do i go about this?

thanks.

 

[Brianwarnock]

What was the error? It may be that I've just missed out a bracket.

Yep the one in red

=IF(AND(C3="IBM",OR(AND(MONTH(F3)=7,YEAR(F3)=2007),AND(MONTH(F3)=8,YEAR(F3)=2007))),J3,0)

I must ask RVD48 if he will understand his formula in a couple of months time, and how easy will it be to change if required?

Brian

 

[rvd48]

i get the just of the formula, but find it hard to change it as it looks fairly complicated now.

 

[rvd48]

hi,

my formula is working fine apart from 1 little problem.
when my list of figures becomes small, such as 0.042, then the averages formula ignores it.

=IF(SUM(AH29:AH58)=0,0,SUMPRODUCT((AH29:AH58>0)*AH29:AH58)/SUMPRODUCT(--(AH29:AH58>0)))


now, this =0 bit i think is the problem. it does the average of figures of over 1 fine, but anything like 0.252 it doesnt pick up.

any help this please?

 

[Brianwarnock]

Hi

Been away on my return found the above post, don't know what it has to do with the rest of the thead but not having seen those constructs before, I leed a sheltered life , I took a look. No doubt you have resolved your problem but as I couldn't get the formula to fail on sample tests the only thing I could come up with was that the cell format containing the formula did not allow for enough decimal places.

Could you take the time to satisfy my curiosity as to the problem/solution?

Thanks

Brian