Make cell blank for exceptions (calculating blood sugars)

cbm550

New Member
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.

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

debooo

Board Regular
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

MrExcel MVP
Would checking for numbers in both cells work?

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

cbm550

New Member
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.

debooo

Board Regular
for a2 use

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

for a4 use

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

is this what you wanted?

cbm550

New Member
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

MrExcel MVP
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

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

cbm550

New Member
Almost debooo.

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

cbm550

New Member
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.

Replies
4
Views
674
Replies
5
Views
387
Replies
18
Views
1K
Replies
0
Views
249
Replies
6
Views
777

1,191,683
Messages
5,987,990
Members
440,124
Latest member
dippy_egg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back