Make cell blank for exceptions (calculating blood sugars)

[cbm550]

I am calculating blood sugars for my daughter and putting them in a spreadsheet. The formula is simple:

Blood sugar - target blood sugar / 20 which yields the amount of insulin that she needs to take. I want this cell to be blank unless I enter a value in the cell above it.

Next, I take the value from that formula and add it to the amount of insulin that she takes for eating.

The value in the top formula can be 0 or less, but, if the value in the bottom one is less than 0, I want the cell to be blank.

I hope someone can give me some help on this.

Thanks.

 

[debooo]

so say blood sugar is in a1 and target blood sug is in a2

try this for the top part =if(isblank(a3),"",(a1-a2)/20)

i dont really understand exactly how you have it setup but try that formula and maybe you can figure out how to change it to fit exactly what you want.

or post an example if you cant

 

[Scott Huish]

Would checking for numbers in both cells work?

=IF(COUNT(B1:B2)=2,(B1-B2)/20,"")

 

[cbm550]

The blood sugar is in cell a1 and the correction units based on that value is calculated in a3.

If the blood sugar is 300 in cell a1 then a3 calculates that 300 subtract 100 (the target sugar) and divide by 20 (the correction factor). This results in a correction of 10 units of insulin. The number for cell a3 can be negative, if the blood sugar is low enough. But I don't want anything in cell a3 until I enter a value into a1

Now, cell a2 is the amount of insulin that is taken for the ingestion of food and that is entered as a value to be added to the value calculated in a3 and displayed in a4. I don't want anything displayed in a4 until there is a value atleast entered into a1 and I don't want anything diplayed in a4 unless it is above 0.

I hope that this explains my situation better.

Thanks for your promt reply.

 

[debooo]

for a2 use

=IF(ISBLANK(A1),"",(A1-100)/20)

for a4 use

=IF((A2+A3)>0,A2+A3,"")


is this what you wanted?

 

[cbm550]

Thanks debooo.

The first one works but the second one displays ##### when the other cells are empty.

But it calculates right. Just does not leave the cell blank.

 

[Scott Huish]

If the blood sugar is 300 in cell a1 then a3 calculates that 300 subtract 100 (the target sugar) and divide by 20 (the correction factor). This results in a correction of 10 units of insulin. The number for cell a3 can be negative, if the blood sugar is low enough. But I don't want anything in cell a3 until I enter a value into a1

Where did it get the 100 from, A2?

And if so, what if there was a value in A1 but not A2, do you still want it to calculate?

 

[debooo]

=IF(AND(ISBLANK(A1),ISBLANK(A2)),"",IF((A2+A3)>0,A2+A3,""))

 

[cbm550]

Almost debooo.

The cell is blank but will not display a positive value.

Thanks for your quick response.

 

[cbm550]

If the blood sugar is 300 in cell a1 then a3 calculates that 300 subtract 100 (the target sugar) and divide by 20 (the correction factor). This results in a correction of 10 units of insulin. The number for cell a3 can be negative, if the blood sugar is low enough. But I don't want anything in cell a3 until I enter a value into a1

Where did it get the 100 from, A2?

And if so, what if there was a value in A1 but not A2, do you still want it to calculate?

The 100 is the target blood sugar. It is a constant value in the formula.

If there is no value in A2, then if A3 is above 0 it should calculate and display the positive number.