between, within, and problems

[cooh23]

I might be staring at this for so long that my brain stopped working. Basically, I have 3 categories called: FPS, PPBS and HgA1C.

Each category has the following (in this order (FPS, PPBS, HgA1C)):

• # of readings (CI3,FU3,JG3)
• # of weeks since last reading (CO3,GA3,JM3)
• Outcome (CM3,FY3,JK3)

So here are the rules:
- If HgA1C reading is greater than 1 and it's within 12 weeks of FPS and PPBS reading, then get the HgA1C outcome.
- If HgA1C reading is greater than 1 and it's greater than 12 weeks of FPS and PPBS reading, then figure out if FPS is less than PPBS, If yes, get FPS outcome, if no, get PPBS outcome.

I am having trouble writing the "Within" 12 weeks.

I would post the sheet but unfortunately I can't due to sensitivity of the data. I hope to hear from you guys soon.

Thanks in advance.

[bentleybob]

So if it's within 12 weeks of FPS but not PPBS (or vice versa), which takes precedent? Or are FPA and PPBS always the same?

[cooh23]

So if it's within 12 weeks of FPS but not PPBS (or vice versa), which takes precedent? Or are FPS and PPBS always the same?

IF HgA1C is within 12 weeks of FPS and PPBS, the value will always be the outcome of HgA1C.

If HgA1C doesn't exist, and FPS and PPBS exist, then figure out which one has the latest value. If FPS>PPBS then FPS.

Thank you.

[cooh23]

here's the attached sheet. Column JR should have the outcome based on the criteria I provided above.

sample.xlsx

[bentleybob]

Still stuck on some of your rules.

Line 14: HgA1C>1 and within 1 week of FBS but not PPBS. You use FBS outcome. Why?
Line 23: HgA1C>1, no PPBS, not within 12 weeks of FBS. You use HgA1C outcome. Why?
etc.

The basic "within" comparison you asked for in your OP is basically:

AND(last week HgA1C >= MAX(last week FBS, last week PPBS) -12,last week HgA1C <= MAX(last week FBS, last week PPBS) +12)

Does that help?

[cooh23]

Still stuck on some of your rules.

Line 14: HgA1C>1 and within 1 week of FBS but not PPBS. You use FBS outcome. Why?
Line 23: HgA1C>1, no PPBS, not within 12 weeks of FBS. You use HgA1C outcome. Why?
etc.

The basic "within" comparison you asked for in your OP is basically:

AND(last week HgA1C >= MAX(last week FBS, last week PPBS) -12,last week HgA1C <= MAX(last week FBS, last week PPBS) +12)

Does that help?

I guess my head was just really confused yesterday, I've been staring at the sheet for so long.
I guess it would help if I provide a background info.

A patient walks into the clinic and gets tested for blood sugar. A client gets placed into a program where the doctor monitors their sugar level. FPS is fasting blood sugar. PPBS is Postprandial Blood Sugar. Most clients will have these two readings. At some point during there program, HgA1c will get taken. Now, HgA1c reading will remain consistent for 3 months(12 wks) that's the reason why HgA1c readings supersedes the other 2 readings if it's within 12 weeks. I am having trouble writing this into formula.

My sample sheet might contain the wrong formula since I was really just testing things out. I haven't tried the code you provided but I will after I post this.

Thank you for looking into this.

John

[cooh23]

The formula you provided helped a lot. I am now getting the outcome I want. However, the problem I am running into now is when the numbers are outside of 12 weeks. I get a "False". I know this is easy but I can't seem to get it to work. Here's what I've tried:

Edit: I am using the following code if HgA1c value falls outside of the 12 week window:

AND(last week HgA1C >= MAX(last week FBS, last week PPBS) +12)

I am seeing progress. I'll post again if I have any problems.

[bentleybob]

Oops. Sorry. Had the > and < switched in the formula I gave you. Can't ever be BOTH less than the MAX minus 12 and greater than the MAX plus 12. You wanted in between.

AND(last week HgA1C >= MAX(last week FBS, last week PPBS) -12,last week HgA1C <= MAX(last week FBS, last week PPBS) +12)

[cooh23]

I got the in between part working but now the problem I have is when I combine my formulas.

Let's assume i'm working on Row 6

If ALL have no data then "No Data"
=IF(AND(JG6=0,FU6=0,CI6=0),"No Data"

IF HgA1c>=1 and it's value is within 12 weeks of fbs and ppbs's values, then HgA1c
IF(JG6>=1,IF(AND(JM6>=MAX(CO6,GA6)-12,JM6<=MAX(CO6,GA6)+12),JK6,

ALL HAVE VALUE HgA1C IS OUTSIDE OF 12 WEEKS

If all readings have value and HgA1c value is outside of 12 weeks of other readings and FBS is less than PPBS then FPS
= IF(AND(JG6>=1,FU6>=1,CI6>=1,CO6<GA6), CM6)


If all readings have value and HgA1c value is outside of 12 weeks of other readings and PPBS>FBS then PPBS
=IF(AND(JG6>=1,FU6>=1,CI6>=1,GA6>CO6), FY6)

If HgA1c<>0 and FBS<>0 and PPBS=0 and HgA1c>12wks then FBS
=IF(AND(JG6>=1,FU6=0,CI6>=1), CM6

If HgA1c<>0 and FBS=0 and PPBS<>0 and HgA1c>12wks then PPBS
=IF(AND(JG6>=1,FU6>=1,CI6=0),FY6

IF HgA1C IS ZERO

If HgA1c=0 and PPBS=0 and FBS>=1 then FBS
=IF(AND(JG6=0,FU6=0,CI6>=1),CM6,

If HgA1c=0 and PPBS<>0 and FBS=0 then PPBS
=IF(AND(JG6=0,FU6>=1,CI6=0),FY6,


If HgA1c=0 and PPBS>=1 and FBS>=1 and FBS<PPBS then FBS
IF(AND(JG6=0,FU6>=1,CI6>=1,CO6<GA6),CM6)

If HgA1c=0 and PPBS>=1 and FBS>=1 and PPBS<FBS then PPBS
IF(AND(JG6=0,FU6>=1,CI6>=1,GA6<CO6),FY6)

Some of them will have 'False' but most work like they're supposed to.

Attached is the sheet. All these formulas are in column JQ

sample.xlsx

[bentleybob]

In JQ7, the reason you get FALSE is because your very last IF statement has the test and the result if the test is TRUE, but nothing if the test is FALSE.

BTW, that's one UGLY formula. If you have more problems with it, I would suggest you break it into pieces and reassemble (if necessary) once everything's been resolved. I realize you have one HUGE spreadsheet, so I understand why you've built a "superformula", but it's a bear to debug, as you undoubtedly have noticed.

[cooh23]

In JQ7, the reason you get FALSE is because your very last IF statement has the test and the result if the test is TRUE, but nothing if the test is FALSE.

BTW, that's one UGLY formula. If you have more problems with it, I would suggest you break it into pieces and reassemble (if necessary) once everything's been resolved. I realize you have one HUGE spreadsheet, so I understand why you've built a "superformula", but it's a bear to debug, as you undoubtedly have noticed.

Haha, tell me about it. I have been working on this for a week and this is the last piece of the puzzle and i'm done.

The problem I am having is that if i enter each formula on each cell, they work just like how it's supposed to but once I combine them, that's when I start having False statements..

[bentleybob]

If you can get it working in pieces, I may be able to combine it back into one formula ...

[cooh23]

If you can get it working in pieces, I may be able to combine it back into one formula ...

Thank you. I will do that and post them first thing tomorrow. i need a break from this.

[cooh23]

I gave up. I tried again yesterday but i get about 90% correct but the rest get the incorrect rules. I'm just gonna do it manually.

I really appreciate all the help that you did Bob. At least now I know how to calculate "within" formulas.

One last question though, how do I get anything outside of the 12? This is where I get my errors. If the numbers are within 12 I get it right.

Thanks,

John

[bentleybob]

Outside of 12 is:

Please Login or Register  to view this content.

Sorry you're still having problems. Seems like you're getting close, Again, I think if you break it into pieces you'll solve it. Then you can either reassemble it or leave it in pieces (in case you need to change it later).

[cooh23]

Outside of 12 is:

Please Login or Register  to view this content.

Sorry you're still having problems. Seems like you're getting close, Again, I think if you break it into pieces you'll solve it. Then you can either reassemble it or leave it in pieces (in case you need to change it later).

That's why I was getting really frustrated because I know i'm really close. haha. I'll come back to it in a couple of days.

Thanks again!

John