I am trying to develop a Calc Formula that will look at one Cell’s numeric value and if that number falls within different ranges make another Cell a predetermined value based upon the range it falls in. To give background I am a Diabetic and am recording my Blood Sugar Levels and when My Blood Sugar falls between certail Levels I have to take a certain amount of Insulin and record both the Blood Sugar Value and the Insulin Value. I have tried and get one range working but cant seem to find the trick when wanting to accomplish this for multiple ranges. What I have for one range that works is as follows =IF(AND(C5>179,C5<230),“24”)
For several ranges you can use VLOOKUP() like this
| A | B | |
|---|---|---|
| 1 | Blood Sugar | Insulin |
| 2 | 150 | 20 |
| 3 | 180 | 22 |
| 4 | 230 | 24 |
| 5 | 250 | 26 |
Given are the inclusive start values of numeric ranges, sorted ascending, VLOOKUP() with only 3 arguments given (or fourth not 0) returns the correspondent of the greatest value that is less or equal to the lookup value. Having a lookup value for example in D1 this
=VLOOKUP(D1;$A$2:$B$5;2)
formula returns
| lookup | return |
|---|---|
| 149 | #N/A |
| 150 | 20 |
| 179 | 20 |
| 180 | 22 |
| 229 | 22 |
| 230 | 24 |
| 249 | 24 |
| 250 | 26 |
| 251 | 26 |
1 Like
Good Morning or whatever time of day it is where you are… OK I am trying like hell to get the VLOOKUP to work and am having problems. I thought I had all the values sorted out but once entered I get Err502 in return in the Cell I am looking to automate the value for. Ive attached a copy of the spreadsheet hopefully what I am doing is making sense to you. If you could help that would be great. Thanks -Brian-
BloodSugar_Log.ods (17.1 KB)
Please try $A$11:$B$511 instead A11:A511
2 Likes
i just tried it and still does not work
Thank You… I have no idea why it wasnt working on my end but it now is… Thanks Again
Note that you do not need to list every single value but the start value of each range is sufficient, see attached BloodSugar_Log.ods (8.7 KB).
2 Likes
AWESOME, Thanks again ive updated it and it is working !
-Brian-