Discussion in 'Business Applications' started by arrrgh2003, Apr 7, 2004.

Not open for further replies.
1. =IF(K2="--------------","check details",IF((AND(I2>=0.01,J2>=1,K2>=1)),PRODUCT(I2:K2),""))

can anyone advise on the above formula?
I am trying to get a product of 3 cells (I2:k2).
but I only want an entry if all three cells (I2:K2) have a number(above 0.01) in them.
and if K2 (from another formula) is not "--------------" (if it is then to insert "check details")
__________________________________________________________

If K2 is not the "---------------" then it should just give the pruduct as long as all 3 cells have numbers in them.

it gives the answer instantly if the J2 figure is entered but I thought the AND formula would wait unitil all three figures are entered?

2. ### Anne Troy

Joined:
Feb 14, 1999
Messages:
11,746
First Name:
Anne
What's the lowest number that'll ever be in I2 through K2?

3. ### XL Guru

Joined:
Aug 30, 2003
Messages:
2,702
>> have I confused you more

Pretty much.

1. I have no idea what "--------------" is meant to be ; a load of minus signs? If I try & enter that, XL tells me there's an error in my formula. I'll leave that bit to you.

2. "it gives the answer instantly if the J2 figure is entered" ; well it didn't for me. It gave me "" until all of I2 - J2 - K2 weren't blank.

=IF(K2="--------------","check details",IF(COUNT(I2:K2)>2,PRODUCT(I2:K2),""))

HTH,
Andy

4. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,255
First Name:
Wayne
looks OK to me - I tried on a spreadsheet and it produced the correct answer.

=IF(K2="--------------","check details",IF((AND(I2>=0.01,J2>=1,K2>=1)),PRODUCT(I2:K2),""))

so I2 has to be = or > then 0.01
J2 has to be = to or > then 1 (you say all I, J, K have to be above 0.01 in post) - so this may be a problem
AND K2 has to be = to or > then 1 (you say all I, J, K have to be above 0.01 in post) - so this may be also be a problem

otherwise it works for me with
I = 0.05
J = 1
K = 2

i get the result 0.1 and only if i enter all three numbers

File size:
1.8 KB
Views:
9