• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

if and Formula not functioning

H

Haroon1

New Member
I have created a formula for my clinic patients. They are divided into 3 main category like ADHERENT, ARV/MISSING, DEFULTER THIS FORMULA IS PICKING ADHERENT AND DEFULT VALUES BUT NOT ARV/MISSING
This is my formula and i have also added Excel sheet
"=IF(AND(T15>=W15,T15<=X15),"ADHERENT",IF(AND(T15>=V15,T15<W15),"ARV MISSING",IF(AND(T15>=U15,T15<=V15),"DEFULTER",IF(T15="","TRANSFER PATIENT",IF(T15="PARV/DEATH","PARV/DEATH",IF(T15="ARV/DEATH","ARV/DEATH"))))))"
 

Attachments

  • conditional formula.xlsx
    47 KB · Views: 5
The only rows which you seem to be highlighting as wrong are rows 3 and 25.
The only column which doesn't have a same value throughout it is column F. Columns G:J each contain the same single value all the way down.
So why should you want row 3 containing 26 Aug 2021 be ARV/MISSING when you have dates either side of that (25 Aug 2021 and 29 Aug 2021) both showing as ADHERENT, apparently correctly; what's special about the 26th Aug 2021?

The same applies to row 25 (2 Sept 2021) which has the day before and the day after both showing (correctly?) as ADHERENT.

The formula is of course working consistently down the whole column.
You need to tell us, in words, what logic you want to be followed (also it might help us understand what the other values mean if you were to put headers in columns G:J).
 
Thank you soo much for your response i have made this formula in this matter if a patient how have to visit the clinic at 2-9-2021 and misses his date then the category should from adherent to ARV/MISSING and turn YELLOW but this not happing the formula should pick date b/w dates.
 
'b/w' = between?
Between what dates?
I'm not sure you realise what's in the file you attached:
All values in column G are identical,
all values in column H are identical,
all values in column I are identical,
all values in column J are identical.

You're expecting me to determine what you want, from a formula that doesn't do what you want!
 
Last edited:
my main issue is only 1 thing if patient is missing a date of 2-9-2021 the date will change its color to Yellow but in the comment section it is not showing ARV/MISSING it is showing it ADHERENT. If you can provide your Gaudiness how to make this formula much batter and Solve the problem which i have mentioned.
 
This is bonkers!
Which columns do I look at to see if a patient (patient = 1 row?) is missing a date of 2-9-21?!
What do the columns represent/mean? I've got 5 columns of dates to look at…
From what I can tell the formula probably works, and it is asking the right sort of questions, but if it's not giving the results you expect, specify such a wrong result and explain why you think it's giving the wrong result.

Otherwise, I give up.
 
You must log in or register to reply here.
Back
Top