close

Privacy guaranteed - Your email is not shared with anyone.

Excel Help - if calc result >2.5, then insert text

Discussion in 'The Okie Corral' started by Deanster, Mar 8, 2012.

  1. Deanster

    Deanster Cheese? Millennium Member CLM

    Joined:
    Feb 24, 1999
    Messages:
    7,383
    Likes Received:
    1,817
    Hi all -

    I should totally know how to make Excel do this, but I'm struggling. Help appreciated!

    I have a fairly simple calculation in many cells of a table, which takes input values, applies a calculation, and then rounds down to half units.

    =FLOOR(((F$6-$G$2)/$C$2)+($B7/$E$2), 0.5)

    and what I want it to do is to return the calculated value if it's less than or equal to 2.5, and to return the text 'Call' if it's above 2.5.

    I'm getting lost in the formulation of the IF/AND statement - I know how to get it to return text strings for True/False booleans, but I can't figure out how to have it return the calculated value in one case, and a text string in another. I'm sure it's simple, but I could sure use a quick assist.

    Thanks!
     
  2. Alcoy

    Alcoy

    Joined:
    Jul 13, 2006
    Messages:
    2,188
    Likes Received:
    773
    Location:
    Virginia
    =IF(A1<=2.5,A1,"Call")
     

  3. Alcoy

    Alcoy

    Joined:
    Jul 13, 2006
    Messages:
    2,188
    Likes Received:
    773
    Location:
    Virginia
    A1 could be all your calc in quote. Hope that helps.
     
  4. CorkyE

    CorkyE

    Joined:
    Jan 14, 2012
    Messages:
    94
    Likes Received:
    2
    Location:
    Ringgold, GA
    That would be my solution... Short and simple.
     
  5. Deanster

    Deanster Cheese? Millennium Member CLM

    Joined:
    Feb 24, 1999
    Messages:
    7,383
    Likes Received:
    1,817
    Hmm... Looks like that's on the right track, but Since A1 (actually G8 in my case) is a formula, not a number, Excel pukes, and says I can't have the formula refer to its own cell, as it creates a circular reference...

    G8=IF(FLOOR(((G$7-$C$3)/$C$2)+($B8/$C$4), 0.5)<=2.5,G8,"Call")

    or am I still doing it wrong?
     
    Last edited: Mar 8, 2012
  6. CorkyE

    CorkyE

    Joined:
    Jan 14, 2012
    Messages:
    94
    Likes Received:
    2
    Location:
    Ringgold, GA
    You have to put the formula in a different cell. If you don't want to see the reference cell, you can hide it.
     
  7. Scared_of_zombies

    Scared_of_zombies

    Joined:
    Dec 2, 2008
    Messages:
    3,369
    Likes Received:
    0
    Could you copy g8 into notepad then put the row of numbers in an adjacent cell and reference that? Or are these going to change in the future?
     
  8. Dennis in MA

    Dennis in MA Get off my lawn

    Joined:
    Aug 16, 2001
    Messages:
    42,700
    Likes Received:
    2,815
    Location:
    Taunton, MA
    Yuuuuuup!

    Been there, done that.
     
  9. Deanster

    Deanster Cheese? Millennium Member CLM

    Joined:
    Feb 24, 1999
    Messages:
    7,383
    Likes Received:
    1,817
    right, so I can't do either of those, at least not simply.

    here's what my spreadsheet looks like

    Three input values - these are changed every couple weeks, and are used in the calculation for every output cell.

    Input Value 1
    Input Value 2
    Input Value 3


    Then a table with gauge values across the top,
    70 80 90 100 120 140 etc

    and down the side how much of a material they're putting in

    10
    15
    20
    25
    etc.

    Each week, we determine the three Input Values for that week, and then several times each day, someone looks at what's in front of them on the gauge, and how much material they're putting in, then uses the chart to get the calculated value of the 2nd material to be used.

    So, each cell in the table has the formula ((Gauge value-Input value 2)/Input Value 1)+(Material 1 amount/Input Value 3) with the result rounded down to the nearest half unit, and referencing the Gauge value and Input Material numbers for its position in the row and column. Each formula is unique to that cell.

    I just want it to do the calculation, and return the calculation as it's currently doing, unless the calculation yields a result over 2.5, in which case I want it to say 'Call', because using more than 2.5 units of the second material requires a check-in.

    Attached is a screen shot showing the calculation for a particular cell, with the various references highlighted
     

    Attached Files:

    Last edited: Mar 8, 2012
  10. IN RedLeg

    IN RedLeg

    Joined:
    Sep 22, 2010
    Messages:
    36
    Likes Received:
    0
    Location:
    Borden, IN
    =IF((FLOOR(((F$6-$G$2)/$C$2)+($B7/$E$2), 0.5)<2.5,(FLOOR(((F$6-$G$2)/$C$2)+($B7/$E$2), 0.5),"Call")

    This should work for you.
     
  11. Cinic

    Cinic Spongy Member

    Joined:
    Nov 17, 2001
    Messages:
    1,915
    Likes Received:
    128
    Location:
    Tempe, AZ
    It's what I was going to suggest.
     
  12. Deanster

    Deanster Cheese? Millennium Member CLM

    Joined:
    Feb 24, 1999
    Messages:
    7,383
    Likes Received:
    1,817
    Thanks! That works!

    And I don't feel so bad about not being able to get that format correct now.

    :)