nested IF formula with multiple conditions based on 2 columns

[Beattle Juice]

nested IF formula with multiple conditions based on 2 columns
this is a blood glucose sheet i am working on the values are copy pasted into a excel sheet from a csv file the meter or meter program makes.

in column D is the blood glucose reading example ( no.s ranging from the 60's to maybe 500 ) (70 to 110 before a meal and 90 to 140 after a meal is the ideal range)

column K is a code 7 = Before Meal and 9 = After Meal or blank when info is put in from older meter or outside sources
column S is where i want the results to appear for conditional format and color code reasons so in order to work right the values in column S need to change as to weather Column K is blank has a 7 or a 9 as to how it figures if a no. in Column D is within target range or not


this formula in column S now -> =IF(AND(D13>=1, D13<=69), "Low", "") & IF(AND(D13>=70, D13<=110), "Good", "") & IF(AND(D13>=111, D13<=130), "High Moderate", "") & IF(AND(D13>=131, D13<=150), "High", "") & IF(AND(D13>=151), "Bad", "")

would work for a before meal reading if it would look into column K and put these results out if there was a 7 there . if there was 9 the range would have to change a little bit and if it were blank might just change the word in column s to know it is just not as accurate reading
i hope i am making a little bit of sense if i can get it do just do something in (S) based on 7 or 9 in (K) with a few ranges simular to above formula i think i can figure it out i seam to be stuck
thanks in advance for any help or pointers

[ConneXionLost]

Perhaps like this:

Code: Select all

=IF(D13="","",CHOOSE(IF(K13=7,MATCH(D13,{0,70,110,130,150},1),IF(K13=9,MATCH(D13,{0,90,140,160,180},1),MATCH(D13,{0,80,125,145,165},1))),"Low","Good","Mod High","High","Bad"))


You didn't provide a different range for when the meal code is not 7 or 9, so I just made one up; please change the values to suit your requirements.

Cheers,

[Beattle Juice]

yes i did not and it i partly cause i am still trying to figure out what best to do when readings are posted from older meters or outside sources sorry for that and thank you so much for your time and effort it may take me a bit to try it out and manipulate it i think i am going to go with when there is no 9 or 7 i will make it just put the value in from the D column so that if it don't say Low,Good, Above Target <--used to be Mod High , High ,Bad it will just reflect the blood glucose reading in another words if it has a word there and not a no. then it means there is more info in the sheet as to weather the person has just eaten which would mean the reading should be higher than if they have not in say 4 hrs again i thank you so much i will try to post a sample sheet as soon as i can the one i have been working on has a lot of junk in it that just shouldnt be posted here atm ..peoples names ph no.s and such but i will try to get one desensitized soon to post should anyone else have used for such