+ Reply to Thread
Results 1 to 12 of 12

Populate a cell if another cell is greater than or equal to a third cell.

  1. #1
    Registered User
    Join Date
    09-20-2012
    Location
    Peoria, Arizona
    MS-Off Ver
    Excel 2013
    Posts
    39

    Red face Populate a cell if another cell is greater than or equal to a third cell.

    I don't even know if this is possible but I thought I would ask you folks. I have attached a screenshot so the following problem makes sense:

    The Cell I am trying to write a formula for is cell C28. I am trying to figure out a formula that will only count Cells C11, C12, C14, C15, & C20 with a variable of "1" if those Cells are equal to or greater than the cells imediately to their left (D11, D12, D14, D15, & D20). Specifically, Cell C11 will only be counted with a varible of "1" if it is greater than or equal to cell D11. If it is less than the value of Cell D11, it will not be counted. Therefore, cell C28 will show a maximum value of "5" if all of the C cells above it are equal to or greater than the D cells. However, if one of the cells is less than the D values, cell C28 will read "4".

    I have spent the greater part of the day searching for this but am at a complete loss on where to even begin. Any ideas?

    Please see attachement. Thanks
    Attached Images Attached Images

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Populate a cell if another cell is greater than or equal to a third cell.

    You can use a pretty simple evaluation, due to the fact that TRUE and FALSE are coerced into 1 and 0 respectively, when exposed to an operation.

    =(C11>=B11)+(C12>=B12)+(C14>=B14)+(C15>=B15)+(C20>=B20)

    In each of those mini equations, the result is TRUE or FALSE, and when we add them all together they instantly turn in 1's and 0's.

    If 3 scores are passing, you might have TRUE+FALSE+TRUE+TRUE+FALSE = 1 + 0 + 1 + 1 + 0 = 3
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    09-20-2012
    Location
    Peoria, Arizona
    MS-Off Ver
    Excel 2013
    Posts
    39

    Re: Populate a cell if another cell is greater than or equal to a third cell.

    Daffodil11,

    I see where you are going with this but I am having trouble linking this all together. When I do your formula, C28 always reads true. I need to convert that to a numerical value then add them up. That is the piece I am not getting. Thoughts?

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Populate a cell if another cell is greater than or equal to a third cell.

    =(C11>=B11)+(C12>=B12)+(C14>=B14)+(C15>=B15)+(C20>=B20) This should evaluate to a number between 0 and 5.

    You can always try a longer route:

    =IF(C11>=B11,1,0)+IF(C12>=B12,1,0)+IF(C14>=B14,1,0)+IF(C15>=B15,1,0)+IF(C20>=B20,1,0)

  5. #5
    Registered User
    Join Date
    09-20-2012
    Location
    Peoria, Arizona
    MS-Off Ver
    Excel 2013
    Posts
    39

    Re: Populate a cell if another cell is greater than or equal to a third cell.

    Thanks for the try. I am still struggling to get this to work.
    error.jpg

    Any other thoughts?

  6. #6
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Populate a cell if another cell is greater than or equal to a third cell.

    Copy B11:C28 into a workbook with the values and formulas, and attach it and I'll take a look.

    There's practically nothing I can with a picture of a spreadsheet, save for comment on your color scheme.

  7. #7
    Registered User
    Join Date
    09-20-2012
    Location
    Peoria, Arizona
    MS-Off Ver
    Excel 2013
    Posts
    39

    Thumbs up Re: Populate a cell if another cell is greater than or equal to a third cell.

    I played around with it and kind of have it working based off of your formula. You ROCK!

    However, if left blank, it counts as a 1. Do you know how to get a blank to default to a 0? I will attach as requested. Thanks
    Attached Files Attached Files

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Populate a cell if another cell is greater than or equal to a third cell.

    deleted post, not valid now
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,939

    Re: Populate a cell if another cell is greater than or equal to a third cell.

    maybe this?
    =SUMPRODUCT((C11:C27>B11:B27)*(C11:C27<>"-"))

  10. #10
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Populate a cell if another cell is greater than or equal to a third cell.

    Or even:

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    09-20-2012
    Location
    Peoria, Arizona
    MS-Off Ver
    Excel 2013
    Posts
    39

    Re: Populate a cell if another cell is greater than or equal to a third cell.

    Thanks Daffodil11! You are one smart dude! God bless.

  12. #12
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Populate a cell if another cell is greater than or equal to a third cell.

    Noncontiguous ranges are a pain! Glad it all worked out!

+ 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. [SOLVED] IF to verify that cell 1 is greater than zero and cell 2 is equal to the exact text, and t
    By NTK_NKGK in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-12-2013, 05:40 PM
  2. [SOLVED] if cell value is greater than or equal to 2 change its value to 1
    By intothewild in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-25-2012, 03:03 PM
  3. Colouring cells based on equal to or greater than value in another cell
    By bexybeeuk in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-14-2011, 11:47 AM
  4. Replies: 2
    Last Post: 03-24-2009, 01:58 AM
  5. Macro to Insert Row if Cell is less than and next cell is greater than or equal to
    By jennifer.l.dean in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-19-2009, 08:35 AM

Tags for this Thread

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