+ Reply to Thread
Results 1 to 10 of 10

Formula showing tested POSITIVE for COVID-19

  1. 04-14-2020, 11:58 AM #1
    Petrusa9
    Petrusa9 is offline
    Registered User
    Join Date
    04-14-2020
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Office 365 Business
    Posts
    3

    Formula showing tested POSITIVE for COVID-19

    Hello,

    Can anyone help me to create a formula for testing people for COVID-19 in quarantine facilities?

    I have attached the excel spreadsheet showing what I want to calculate and display.

    My idea is to display only POSITIVE cases in the cell 'Current Status - POSITIVE?' saying 'Yes' and highlight in RED.

    I need a formula which reflects the latest tests and displays 'Yes' if people tested POSITIVE. Once the person has been tested POSITIVE needs two tests NEGATIVE to be allowed to go to home quarantine.

    Thank You!
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-14-2020, 12:14 PM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,916

    Re: Formula showing tested POSITIVE for COVID-19

    Welcome to the forum.

    In P3 copied down:

    =IF(AND(INDEX($C3:$O3,MATCH("zzzzzzzzzzzzz",$C3:$O3))="No",INDEX($C3:$O3,MATCH("zzzzzzzzzzzzz",$C3:$O3)-2)="No"),"","Yes")

    And the CF rule:

    Cell value contains "Yes"
    Attached Files Attached Files
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 04-14-2020, 03:03 PM #3
    FDibbins
    FDibbins is offline
    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,938

    Re: Formula showing tested POSITIVE for COVID-19

    WE have an excel formula that can test for Corona? I knew excel was good, but...wow!!

    (JK)
    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
    Reply With QuoteRegister To Reply
  4. 04-14-2020, 03:25 PM #4
    Petrusa9
    Petrusa9 is offline
    Registered User
    Join Date
    04-14-2020
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Office 365 Business
    Posts
    3

    Re: Formula showing tested POSITIVE for COVID-19

    Many thanks for your quick reply. Your help is highly appreciated. But there is something that I wasn't thinking about. The formula works only if there are two tests already. If we have only one test (entry test) then Excel shows #VALUE! error. Could you help me with this, please?
    Reply With QuoteRegister To Reply
  5. 04-14-2020, 04:56 PM #5
    Limor_OP
    Limor_OP is offline
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Formula showing tested POSITIVE for COVID-19

    try this one in cell p3
    =IFERROR(IF(INDEX(C3:O3,MAX(MATCH(REPT("z",255),C3:O3)))="Yes","Yes",IF(AND(INDEX($C3:$O3,MATCH(REPT("z",255),$C3:$O3))="No",INDEX($C3:$O3,MATCH(REPT("z",255),$C3:$O3)-2)="No"),"","Yes")),"")

    Regarding the red highlight:
    you can do this in conditional formatting. Please let me know if you need guidelines how to define that.

    Good Luck.
    Last edited by Limor_OP; 04-14-2020 at 05:21 PM.
    Reply With QuoteRegister To Reply
  6. 04-15-2020, 07:46 PM #6
    Petrusa9
    Petrusa9 is offline
    Registered User
    Join Date
    04-14-2020
    Location
    Edinburgh, Scotland
    MS-Off Ver
    Office 365 Business
    Posts
    3

    Re: Formula showing tested POSITIVE for COVID-19

    Many thanks for your help. This saved me a lot of time
    Reply With QuoteRegister To Reply
  7. 04-16-2020, 12:43 AM #7
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,916

    Re: Formula showing tested POSITIVE for COVID-19

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  8. 04-16-2020, 12:49 AM #8
    mdrungys
    mdrungys is offline
    Registered User
    Join Date
    04-13-2020
    Location
    Redwood City, CA
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    6

    Re: Formula showing tested POSITIVE for COVID-19

    Is this information publicly available?
    Reply With QuoteRegister To Reply
  9. 04-16-2020, 12:50 AM #9
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,916

    Re: Formula showing tested POSITIVE for COVID-19

    What information?
    Reply With QuoteRegister To Reply
  10. 04-16-2020, 03:52 PM #10
    mdrungys
    mdrungys is offline
    Registered User
    Join Date
    04-13-2020
    Location
    Redwood City, CA
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    6

    Re: Formula showing tested POSITIVE for COVID-19

    Sorry for a vague question and not associated with Excel. The question is directed at Petrusa9, is the COVID-19 testing facilities and the numbers behind cases publicly available?
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula Showing Average Pace in Minutes for Positive Splits Falling Into a Set Time
    By hatkevtonin in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-19-2018, 02:37 PM
  2. Pivot table showing percentage of positive answer according to quintile
    By Sir_Arcturus in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 09-29-2015, 09:38 AM
  3. VBA IsError() Showing a false positive. Advice needed
    By Sp1ral0u1 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-20-2015, 03:49 PM
  4. Excel 2007 : Fomula showing as a positive rather than a negative
    By docrobo in forum Excel General
    Replies: 9
    Last Post: 03-17-2012, 03:02 PM
  5. Showing a + symbol for positive numbers
    By avidcat in forum Excel General
    Replies: 2
    Last Post: 12-22-2010, 09:21 AM
  6. Showing a negative as a positive and a positive as a negative number
    By avidcat in forum Excel General
    Replies: 2
    Last Post: 11-13-2008, 12:35 AM
  7. Add a '+' to a formula showing positive variance
    By doowop5000 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-08-2007, 09:44 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1