+ Reply to Thread
Results 1 to 8 of 8

INT Funtion within a formula

  1. #1
    Registered User
    Join Date
    03-09-2012
    Location
    ft worth, tx
    MS-Off Ver
    Excel 2003
    Posts
    27

    INT Funtion within a formula

    The only issue I am still having is how to incorporate the INT function so that I get whole number without the decimals. I have formatted the cell the formulas below still see the decimal points and it is throwing the data off. I have tried numerous combinations and can't figure out how to incorporate the INT function into:

    =LOOKUP(A2,{0,75,100,150,200},{1,2,3,4,5})


    As well as:

    =IF(C9<75,1,IF(AND(C9<=99,C9>=75),2,IF(AND(C9<=149,C9>=100),3,IF(AND(C9<=199,C9>=150),4,5))))

    Thanks again all!

  2. #2
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: INT Funtion within a formula

    They formulas return whole numbers!

    If they were returning decimals then wrap them in the INT Function:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: INT Funtion within a formula

    Where do you want to use INT?

    Is it for A2 in the first formula?

    =LOOKUP(INT(A2),{0,75,100,150,200},{1,2,3,4,5})

  4. #4
    Registered User
    Join Date
    03-09-2012
    Location
    ft worth, tx
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: INT Funtion within a formula

    I am trying to reference A2 which is a formula that pulls an average over 31 sheets. The formula I am trying to write takes whatever is in A2 and determines the range that it falls into. The problem is that (even with cell formatting down to zero decimal places) the formulas I posted above still the the decimal places and it acts accordingly.

    I tried Kevins: =INT(LOOKUP(A2,{0,75,100,150,200},{1,2,3,4,5}))

    It still returns the same number as if it still sees the decimal places. The actual formula and data I am using follows (The above was a generic formula given to me by a Mod):

    =LOOKUP(C9,{0,6,12,15,24},{1,2,3,4,5})

    The ranges are:

    0-5 returns a 1
    6-11 returns a 2
    12-14 returns a 3
    15-23 returns a 4
    Greater than 23 returns a 5

    The problem comes in when C9 takes the average of 14.65 and makes it a 15 (example). The formula above still sees the 14 and gives it a three when it should be a 4.

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: INT Funtion within a formula

    If you want to round up, use the ROUNDUP function, not INT.

    =INT(14.65) = 14

    =ROUNDUP(14.65,0) = 15

  6. #6
    Registered User
    Join Date
    03-09-2012
    Location
    ft worth, tx
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: INT Funtion within a formula

    I don't necessarily want to round up in every case. If the number reaches xx.5 I want it to round up...if not I want it to round down. I thought INT would do that for me. If INT goes down to the nearest whole number and ROUNDUP goes to the nearest whole number away from 0..which function should I use to round accordingly?

    Also..how would that fit into the formula I posted above? Thanks.

  7. #7
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: INT Funtion within a formula

    Sounds like you just need to ROUND, i.e.

    =LOOKUP(ROUND(C9,0),{0,6,12,15,24},{1,2,3,4,5})

    or you could use MATCH to shorten

    =MATCH(ROUND(C9,0),{0,6,12,15,24})

    .....alternatively just use the ROUND function around your C9 formula and the LOOKUP can remain as is.....
    Audere est facere

  8. #8
    Registered User
    Join Date
    03-09-2012
    Location
    ft worth, tx
    MS-Off Ver
    Excel 2003
    Posts
    27

    Re: INT Funtion within a formula

    Thanks Daddylonglegs! This worked perfectly. Rep added.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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