help with formula please

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

arrrgh2003

Thread Starter
Joined
Dec 4, 2003
Messages
83
=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?

Am I going about this right or have I confused you more? I have trouble relating things in my head!?!?!?
 
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.

We probably need more info, however try

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

HTH,
Andy
 

etaf

Wayne
Moderator
Joined
Oct 2, 2003
Messages
65,471
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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top