View Full Version : Excel Help: How to determine if a group of numbers lie in a range.

Rasta

7th October 2008, 08:28 PM

Right... I'm stuck.

I have several columns in which I have a whole list of numbers in excel. Each column is a patient and in that column is a variable number of blood glucose readings (some have 36 readings others have 7).

What I need to do is work out for each patient (i.e. column) what percentage of their readings lie in the range 5-12.

I have no idea how to work this out, I could do it manually, but there are alot of patients and I can't be bothered going through them all individually.

Surely there is a simple way in excel to work out what percentage of the column lies within the range. Does anyone know how I can do it?

Muchas gracias. :thumbup:

Huy

7th October 2008, 08:45 PM

If the patient identity doesn't matter (row), why don't you sort the column in asc/descending and do a count. Or a simple selection (start at 5 first selection, stop at 12 last selection, SHIFT top to bottom (select all those relevant ones) and you've highlighted the ones that fall between 5-12).

Then you've got your percentage (count/total x 100).

Where total is the number of rows that exist, i.e. patients.

...or you can look at the DCOUNT function.

rtc

7th October 2008, 08:51 PM

Imagine column a had rows 1 to 4 blank and row 5 was the patient name and rows 6 onwards were the readings. Imagine the max number of readings for the patient with the most readings did not exceed 3000.

In cell a4 type:

=(count(a$6:a$3005)-countif(a$6:a$3005,">12")-countif(a$6:a$3005,"<5"))/(count(a$6:a$3005)

Format cell a4 as a percentage then fill across to the right.

This gets the count of the cells with something in them, subtracts the number of cells with less than 5, subtracts the number with more than 12 and divides the lot by the number of cells with readings. If it doesn't work try using counta instead of count (as I don't have excel in front of me to test it).

Rasta

7th October 2008, 09:09 PM

Imagine column a had rows 1 to 4 blank and row 5 was the patient name and rows 6 onwards were the readings. Imagine the max number of readings for the patient with the most readings did not exceed 3000.

In cell a4 type:

=(count(a$6:a$3005)-countif(a$6:a$3005,">12")-countif(a$6:a$3005,"<5"))/(count(a$6:a$3005)

Format cell a4 as a percentage then fill across to the right.

This gets the count of the cells with something in them, subtracts the number of cells with less than 5, subtracts the number with more than 12 and divides the lot by the number of cells with readings. If it doesn't work try using counta instead of count (as I don't have excel in front of me to test it).

Awesome... that worked a treat!

Thank you so much!

richbowen

7th October 2008, 09:11 PM

Similar approach to rtc, but each part of the calculation on a different row

http://img.skitch.com/20081007-kasrxfbxnw6g4x61rndtyed2sn.preview.jpg (http://skitch.com/richard.bowen/a6ay/workbook1)

Click for full size (http://skitch.com/richard.bowen/a6ay/workbook1) - Uploaded with plasq (http://plasq.com)'s Skitch (http://skitch.com)

rtc

7th October 2008, 09:12 PM

Awesome... that worked a treat!

Thank you so much!

No worries. Glad it worked straight off without me having to test it.

Aladdin

7th October 2008, 09:16 PM

=(COUNTIF(a5:A50,">=5")-COUNTIF(A5:A50,">12"))/COUNT(A5:A50)

Logical description follows:

number of cells from a5 to a50 whose values are 5 or greater

less number of cells from a5 to a50 whose values are greater than 12

then divide result by the number of cells from a5 to a50 which have a non-zero value.

See post above. Similar concept - create some space at the top of the spreadsheet (insert rows).

My formula assumes you've got your data from row 5 to row 50 (whatever your requirements are).

Paste above formula into a blank cell at top of column A. Copy to the right.

Format this row of cells as percentage.

Confirmed - works on Excel Mac 2008.

Edit: must remember to refresh page just before posting.

Powered by vBulletin® Version 4.2.5 Copyright © 2019 vBulletin Solutions Inc. All rights reserved.