creating a formula that compares 2 outcomes

Hi,

I'm really stuck with this one, I have a formula that compares data from yesterday, and if it meets the criteria it gives me an alert. However, I need to change that to compare the data to 2 days prior and to yesterday and returning an outcome based on which of the 2 is greater than 20% for the FiO2 outcome. The same will be for the PEEP outcome, to return an alert if the value is equal to or less than 2.9. Attached is the spreadsheet, please let me know if this makes any sense.

You need to define expected results for all possible outcomes.
For example, when you compare today's data to yesterday and day before, there are various possibilities.
Both could be over 20%, either one could be over 20%, both could be under 20% etc.
Make a table with possible inputs and respective outputs.
Same would go for the PEEP.

I find your file confusing as you have Day 3 FiO2 then revert back to Day 2 ...

but in A16

=IF(AND(B15<>0,B11<>0),IF(MAX(B15-B11,B15-B6)>=0.2,"Alert Alert","Good"),"")

????

.... based on which of the 2 is greater....

Alert if EITHER > 0.2 ??

Confused!!!

Each pt. has a starting stat, after the first day the numbers are then compared to the previous day outcome.

I need to compare the previous 2 days and the lesser than 20% of the two days gets the alert:

Day 1
FiO2 40%

Day 2
FiO2 60% Bad

Day 3
FiO2 60% (still bad because it is comparing to day 1)

Day 4
FiO2 60% Good (is now comparing to Day 2)

Does that explains it better?

I am sorry FDibbins, do you mean to "Reply with Quote"?

I got it, so far is working great!!! I will let you know as soon as enter more data... Thank you so much!!!!

Originally Posted by modytrane

I have updated the file with correct formulas for FiO2. I can't seem to find a way to attach the file.
I know I have done it before. Must be missing something.
Tell me how to attach the file.

I got the file, thank you so much!!!

Originally Posted by Riverstar5

I got the file, thank you so much!!!

Is it possible that you can explain the formula so that I can learn what the formula is actually doing? Thank you!!!

=IF(B15<>0,IF(OR((B15-B9)>=0.2,(B15-B5)>=0.2),"Alert Alert","Good"),"")

First IF will make sure that B15 has a non-zero value. IF not, the formula will result in a blank.
IF B15 is non-zero, then next IF will check to see if B15 is more than 20% higher than either B9 [Day before] or B5 [2 Days before]. If B15 is higher than either B9 or B5 by more than 20% then formula will result in "Alert Alert", otherwise it will result in "Good".
Hope that helps.

Originally Posted by modytrane

First IF will make sure that B15 has a non-zero value. IF not, the formula will result in a blank.
IF B15 is non-zero, then next IF will check to see if B15 is more than 20% higher than either B9 [Day before] or B5 [2 Days before]. If B15 is higher than either B9 or B5 by more than 20% then formula will result in "Alert Alert", otherwise it will result in "Good".
Hope that helps.

that was awesome!!! It even taught me how to create similar formulas!!! Thank you so much for your time!!