Excel formula guru out there?

[Gawdzilla Sama]

I need help with a spread sheet. I'm supposed to check my blood sugar four times a day now, and I want to use a spreadsheet to keep score.

The first column will have a "good" range of 80-130 and I'd like to have a representation of how my blood sugar compares to that range with plus or minus percentages. Say the blood sugar was 180. That would be +mumblecuss% (180/130 = 34% over "good"), but I need this to cover a bunch of data points, 30 to 90 usually.

What's the best way to do that?

[Xamonas Chegwé]

I think this should work. Been ages since I wrote any Excel though...


Assuming A1 has your daily reading...

In A2:

=IF(AND(A1>= 80, A1 <= 130), "Good", "Bad") Will show Good if in range, else Bad

In A3:

=IF(AND(A2 = "Bad", A1 < 80), "-"&(A1/80)*100&"%", (A1/130)*100&"%") If A2 is bad will show %age below 80 or above 130 depending.


If it doesn't work, talk to my lawyer!

[Xamonas Chegwé]

Oh - and format A3 to show numbers in whatever format you need - ie. decimal places, etc.

[Clinton Huxley]

Paging KLR

[rasetsu]

I'd do it with vlookup(), but if the table is small, if/then's work, too. It's been ages, but I was a top notch macro builder in my day. I built shit that made "advanced spreadsheet" people lose all control of their bowels.

[Rum]

Ready made free ones to download here if they are of any interest: http://www.diabeticconnect.com/discussi ... readsheets

[klr]

I think this should work. Been ages since I wrote any Excel though...


Assuming A1 has your daily reading...

In A2:

=IF(AND(A1>= 80, A1 <= 130), "Good", "Bad") Will show Good if in range, else Bad

In A3:

=IF(AND(A2 = "Bad", A1 < 80), "-"&(A1/80)*100&"%", (A1/130)*100&"%") If A2 is bad will show %age below 80 or above 130 depending.


If it doesn't work, talk to my lawyer!

One additional thing: "Fixed" figures are often better held in reference cells somewhere else on the sheet, or maybe on a second sheet on the workbook. That way, if you ever need to change (say) 80 and/or 130, you won't need to change every cell that references them. Even with mass copying, that'd be a chore. Just change the reference value itself.

Ready made free ones to download here if they are of any interest: http://www.diabeticconnect.com/discussi ... readsheets

Damn, we should have remembered the "someone's probably already done it" option.

These look quite detailed and feature-laden (ridden?), but if they're not quite what you need, and you're not a spreadsheet maven ...

I suggest concentrating on the simple part first: Making sure that you're recording the data as you want, for the duration that you want, for the intervals that you want (4 x times per day, for however many days). Do you want to have a single line per day with columns for each reading during the day (as per some of those spreadsheets), or just one line per reading? The latter option is much easier to work with if you want to add in complex formulae such as XC has suggested.

[Gawdzilla Sama]

Thanks, folks. I'll be recording the data they asked for, of course. They gave me a sheet of paper with squares to fill in, this is just for my own use in goal setting.

And, Kev, the fixed numbers are really that in this case, but your thought is a good one, I got that advice in a class back in 1989. The formula will only be used two places, so I can stick the numbers in without too much fret.

[Calilasseia]

I just took a look at this.

Below a snapshot of my test Excel spreadsheet, which I've annotated in PhotoShop to highlight the respective features. Refer to this when considering the instructions below.

[1] Column A contains your test dates (and times if you're performing tests more than once per day).
[2] Column B contains your test results.
[3] Column C contains the test status - whether the results are low, normal or high.

Now, here's the steps you take.

[1] Format the first data cell in Column A with your desired date format, as shown in the annotation below.

[2] Copy and paste the following formula into the first data cell in Column C (in the test spreadsheet, this data cell is cell C5, and the data it refers to in order to extract the status is cell B5):

=CHOOSE((if((b5<$b$1),-1,0)+if((b5>$b$2),1,0)+2), "LOW" , "Normal" , "HIGH")

[3] Whilst that cell into which you've pasted the formula is highlighted, select from the menu the menu item:

"Format -> Conditional Formatting ..."

You'll now be presented with a nice dialogue box with multiple tabs. Fill in the data as shown in my annotated version below (for each new condition, you'll have to click on the "Add" button in order to add a new condition). Once you've entered the condition data, click on the "Format ..." button to change the cell's visual fomatting details accordingly - this will pop up another dialogue box with lots of options).

[4] Each time you add a new entry into the table, copy and paste the entire contents of the previous status cell in Column C, so that the new data in the adjacent Column B is flagged accordingly.

PS: If you want a copy of the spreadsheet to tinker with, I can mail it to you.

[Xamonas Chegwé]

I don't like your method, Cali. Too much green.

[Gawdzilla Sama]

Wha?

[Calilasseia]

I don't like your method, Cali. Too much green.

That's just my desktop colours. About which I've said much over at the old Richard Dawkins forums.

Basically, 'Zilla can tinker with the format to his heart's content once he knows how to do it. The above should provide all the details.

[Calilasseia]

Wha?

The idea is this - test results that are below the safe minimum are highlighted in blue text, normal readings are left without a highlight in the "Status" column, and readings above the safe maximum are highlighted in red text. The formula generates the appropriate text for each test result, either "LOW", "Normal", or "HIGH", and the conditional formatting applied to the cell generates the highlighting depending upon the end result.

[Gawdzilla Sama]

Here's what I've been doing.

[Calilasseia]

You can adapt my highlighting to that table. Though from the looks of it, all your results are going to be flagged as "HIGH" using my highlighting, because even without medical training, that lot of readings is in low Earth orbit.