Status
Not open for further replies.
1 - 4 of 4 Posts

#### arrrgh2003

· Registered
Joined
·
83 Posts
Discussion Starter · ·
=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?

#### Anne Troy

· Registered
Joined
·
11,836 Posts
What's the lowest number that'll ever be in I2 through K2?

#### XL Guru

· Registered
Joined
·
2,708 Posts
>> 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

#### etaf

· Super Moderator
Joined
·
65,991 Posts
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

#### Attachments

• 1.8 KB Views: 10
1 - 4 of 4 Posts
Status
Not open for further replies.