1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

help with formula please

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

Thread Status:
Not open for further replies.
Advertisement
  1. arrrgh2003

    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!?!?!?
     
  2. Anne Troy

    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

    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.

    We probably need more info, however try

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

    HTH,
    Andy
     
  4. etaf

    etaf Moderator

    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
     

    Attached Files:

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/218177

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice