Math/Excel Formula Wizards

NYSpeed Automotive NYSpeed Off Topic
1

I’m working on creating a Excel (Google Sheets actually) version of my brother’s diabetes chart. (SEE HERE)

So basically the Bolus cells are the total after you figure out the calculation.

The BG is Blood Glucose taken from the blood sugar tester. That number is a static number
Carbs is based off of the food you’re eating. The conversion to insulin is One unit for every 8 Carbs or 1:8. It’s 1:12 for lunch time at school.
Then if he’s over a certain amount of blood sugar (BG cell) he’ll have to do a correction (more units of insulin) It’s 1 unit for every 40/120BG

So the total should be something like

(Carbs/8)
+
(If BG < 120, then 0. If BG > 120, For Every 40, add 1) <- obviously this part I need help with.

2

Might have figured this out myself. Here is the formula for the very first Bolus cell (E5)

=IF(AND(E3&lt;=120),E4/8,IF(AND(E3&gt;120,E3&lt;=160),E4/8+1,IF(AND(E3&gt;160,E3&lt;=200),E4/8+2,IF(And(E3&gt;200,E3&lt;=240),E4/8+3,If(AND(E3&gt;240,E3&lt;=280),E4/8+3,IF(AND(E3&gt;280,E3&lt;=320),E4/8+4,IF(AND(E3&gt;320,E3&lt;=360),E4/8+5,IF(AND(E3&gt;360,E3&lt;=400),E4/8+5,IF(AND(E3&gt;400,E3&lt;=440),E4/8+6,IF(AND(E3&gt;440,E3&lt;=480),E4/8+7,IF(AND(E3&gt;480,E3&lt;=520),E4/8+8)))))))))))