Need to add a IF Statement to another IF Statement.

[wkirkcrawford]

Greetings,

I have my Blood Sugar Log that I need to add an IF Statement to a cell that has 2 IF Statements.
=IF(K23="","",IFERROR(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE($V$11,"Morning",REPT(" ",255),1),255))," ",REPT(" ",255)),99)+0,""))

I need to check back a day at lunchtime to see if I was over 180. The cell is V22, i.e. (IF V22>189, 28,)

I don't know where I need to add this IF Statement. The first IF is to keep cell K23 blank. The second IF is to look up the amount to take.

W. Kirk Crawford
Tularosa, New Mexico

 

[Alex Blakenburg]

If k23 is blank AND V22 > 189, do you want to show "" or 28 ?

 

[Alex Blakenburg]

Since I am in a different time zone it might save time if I give you a couple of options.
I find it easier to follow the formula if you wrap the whole working part in the IFERROR statement.
(This of course won't work if you are using the error result part of the IFERROR as part of a calculation or in another If statement.)

Option 1 - If K23 is blank return blank regardless

=IFERROR(
   IF(K23="","",
      IF(V22>180,28,
         RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE($V$11,"Morning",REPT(" ",255),1),255))," ",REPT(" ",255)),99)+0)),
"")

Options 2 - If V22 > 186 return 28 even if K23 is blank

=IFERROR(
   IF(V22>180,28,
      IF(K23="","",
         RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE($V$11,"Morning",REPT(" ",255),1),255))," ",REPT(" ",255)),99)+0)),
"")

 

[wkirkcrawford]

Alex,

Thank you for your illustrated example. That's great.

But there is one little issue, if I am higher than 180, it turns on the following morning at 28. It should be blank.

It doesn't seam to matter which forumla is use.

W. Kirk Crawford
Tularosa, New Mexico

 

[Alex Blakenburg]

I don't have enough information on what you are doing.
It is a static formula that is dependent on the values in K23, V23 & V11.
What is it that you are expecting to change these values ?

 

[DanteAmor]

Here is another formula for you to consider

=IF(K23="","",IFERROR(IF(LOOKUP(9E+99,--RIGHT(LEFT($V$11,SEARCH("Morning",$V$11)-1),ROW($1:$99)))>180,28,""),""))

 

[wkirkcrawford]

I don't have enough information on what you are doing.
It is a static formula that is dependent on the values in K23, V23 & V11.
What is it that you are expecting to change these values ?

Alex,

K 23 is my blood sugar read in the morning. V 23 should be blank, it's my reading for lunchtime, which hasn't come yet. V 11 is reading a line in my instructions of how many units of insulin I am supposed to take.

I can't get the file to download, nor can I take a screen dump. Sorry.

W. Kirk Crawford
Tularosa, New Mexico

 

[Alex Blakenburg]

I am not really sure how your spreadsheet is supposed to work.
Borrowing from @DanteAmor's formula and just parking some of the additional logic you want for a moment,
does this structure help you at all ?
Presumably in the Morning part we put some additional logic to return 28 based on the previous row in column V.

=IF(MATCH(99^9,K:K)>MATCH(99^9,V:V),
     "Morning: "&INDEX(K:K,MATCH(99^9,K:K),0),
     "Night: "&INDEX(V:V,MATCH(99^9,V:V),0))