+ Reply to Thread
Results 1 to 5 of 5

If a number falls within a range formula & separate spreadsheet

  1. #1
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    If a number falls within a range formula & separate spreadsheet

    Hello,

    It would be amazing if anyone could help me out with the following two formulas.

    1) I have a set of 5 different sums in their own cells in 2000 rows

    K1 - L1 - M1 - N1 - O1
    23 - 45 - 110-120- 150
    10 - 25 - 101-119- 129
    etc

    I'd like to get if K1 falls within the range of 1-20. If t does, get a checkmark (or any other symbol that indicates that it does). If it doesn't fall within that range, to get a cross or an X symbol.
    If it helps in any way, the Character code for the checkmark is 252 and for the X is 206.


    2) Also I have the following formulas after the help of other members.

    =SUM(COUNTIF(B3:G3,{2,3,5,7,11,13,17}))

    =COUNTIF(CB3:CB60, CHAR(206))

    =COUNTIF(BB3:BB69,1)

    These formulas are used in a sheet called Analysis. Is it possible to get the results in a separate sheet called +Analysis?

    If anyone has the answer to any of the above please post it.

    Thanks
    Sans
    Last edited by sans; 10-24-2011 at 04:05 AM.

  2. #2
    Forum Contributor
    Join Date
    08-01-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    182

    Re: If a number falls within a range formula & separate spreadsheet

    1. One of the way to do this is to put a Checkmark symbols in any of the cells say A1. So the formula will be

    Please Login or Register  to view this content.
    Please ensure that you use the same font in the result cell as you have used in A1.

    2. Create a new sheet and then use these formulas there with reference to the Analysis sheets. Example your first formula willl look like

    Please Login or Register  to view this content.
    Hope it helps.

    Cheers-
    Inayat
    One must rise above the Clouds to see the Blue Sky rather than constantly trying to push them aside

    If you want to say Thank you to a member, click the little star * below.

    If your Problem is Solved, please mark the Thread as Solved

  3. #3
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Re: If a number falls within a range formula & separate spreadsheet

    Thank you for your help.

    The =SUM(COUNTIF(Analysis!B3:G3,{2,3,5,7,11,13,17})) works great.

    With the =IF(AND(K1>=1,K1<=20),A1,"X") formula I keep getting a circular reference error. Am I doing something wrong?

    Thank you
    Sans

  4. #4
    Forum Contributor
    Join Date
    08-01-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2007
    Posts
    182

    Re: If a number falls within a range formula & separate spreadsheet

    Hi Sans,

    In which cell did you input the formula.

    Cheers-
    Inayat

  5. #5
    Valued Forum Contributor sans's Avatar
    Join Date
    10-19-2011
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    550

    Re: If a number falls within a range formula & separate spreadsheet

    Hi inayat, circular reference was my bad. Thank you very much for the formula. It works great now!

+ 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