+ Reply to Thread
Results 1 to 7 of 7

IF formula help?

  1. #1
    Registered User
    Join Date
    11-07-2019
    Location
    Sheffield, England
    MS-Off Ver
    Numbers
    Posts
    3

    IF formula help?

    Hi, new to the forum.

    Trying to get my head around spreadsheets, and I've hit a brick wall.

    I want a formula which expresses a value as 0,1,3 should the data be less than -0.30, between -.3 and +.3 or greater than +0.30.

    So, for example...

    If cell E2 is -0.1, I want cell I2 to display 1...because the value is between -.30 and +.30.

    If cell E2 is -0.5, I want cell I2 to display 0... because the value is less than -.30.

    If cell E2 is 1.1, I want cell I2 to display 3...because the value is greater than +.30.

    I'm assuming it's an IF formula, but I keep coming up with errors in the formula.

    Any help would be appreciated.

    Regards

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: IF formula help?

    It can be an IF formula or you could try this

    =LOOKUP(E2,{-9.99999999999999E+307,-.30,.30},{0,1,3})
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    11-07-2019
    Location
    Sheffield, England
    MS-Off Ver
    Numbers
    Posts
    3

    Re: IF formula help?

    Thank you, works perfectly.

    Could you explain how that works for my understanding? Why -9.999999999999E +307?

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: IF formula help?

    That is just a very large (in magnitude) negative number. If your numbers are quite small in magnitude, you could use something like -100 instead.

    Hope this helps.

    Pete

  5. #5
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: IF formula help?

    The LOOKUP matches E2 against -9.99999999999999E+307 -.30 and .30
    -9.99999999999999E+307 is the lowest negative number Excel can store.

    If E2 is >= -9.99999999999999E+307 and < -.30 then the first result is returned from the second pair of brackets, ie 0.
    If E2 >= .30 and < .30 then the next number is returned, ie 1
    If E2 >= .30 then the third number is returned, ie 3

    Think of
    -9.99999999999999E+307
    -.30
    .30

    being the start number of a range of numbers

    You could also have said

    IF(E2>.30,3,IF(E2 >=-0.30,1, 0))

  6. #6
    Registered User
    Join Date
    11-07-2019
    Location
    Sheffield, England
    MS-Off Ver
    Numbers
    Posts
    3

    Re: IF formula help?

    OK I see.

    The IF formula at the end is very close to what I was trying originally, but I was trying to put between values for the "1".

    By my understanding, the last formula is essentially saying... If E2 is >0,3, award 3. If that is not the case, but it is greater than -0.3, award 1, anything else (i.e. less than -0.3), award 0.

    Many thanks again.

  7. #7
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: IF formula help?

    Re last formula, yes thats right.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 01-10-2019, 09:34 AM
  2. Replies: 5
    Last Post: 02-16-2018, 06:50 AM
  3. Replies: 8
    Last Post: 09-22-2017, 05:41 AM
  4. Excel formula bar to display the result of the formula , not the formula?
    By max_max in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-18-2016, 07:15 PM
  5. Replies: 11
    Last Post: 06-06-2014, 03:34 PM
  6. how to hide formula in formula box, view lookup result in formula box?
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 04:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1