SEARCH

The How-To Geek Forums Have Migrated to Discourse

How-To Geek Forums / Microsoft Office

(Solved) - Excel: Sums of If Statement Values

(19 posts)
  • Started 1 year ago by Se7ven
  • Latest reply from Se7ven
  • Topic Viewed 1769 times

Se7ven
Posts: 10

For someone using Excel to keep an ongoing record of their blood pressure in two columns. The first of these is for systolic and the second for diastolic. At the bottom of each of these columns it is easy to determine the high, low and average of these readings. However, another item that is needed to be added is, which of these readings falls within one of 4 categories and then sum the total of such values. Although an if statement would cover the greater, or less than value. How does one then sum these all up keeping in mind the addition of rows? Rows are added each time a reading is taken so the tally is one that is ongoing.

Day Systolic Diastolic

1 x y

2 x1 y1

High

Low

Average

Desirable = If cells B2 - B~ is less than 10 but greater than 5

Caution = If cells B2 - B~ is less than 20 but greater than 10

Warning = If cells B2 - B~ is less than 30 but greater than 20

Red Alert = If cells B2 - B~ is less than 40 but greater than 30

Posted 1 year ago
Top
 
moreeg
moreeg
Posts: 842

Hi

Taking a guess at your format you have ...

Column A = Systolic
Column B - Diastolic
Column C = Category

To count the sum of each category you can use

=Countif(C:C,"Desirable")
=Countif(C:C,"Caution")

and so on for the other categories.

If this isn't what you're after could you post a screen shot of your worksheet?

Moree

Posted 1 year ago
Top
 
Se7ven
Posts: 10

http://picpaste.com/d339775d71.....3e70b5.JPG

Blood Pressure Classifications

Systolic Diastolic Category
115 75 Desirable
120-139 80-89 Prehypertension
140-159 90-99 Hypertension Stage 1
160 or over 100 or over Hypertension Stage 2

Posted 1 year ago
Top
 
moreeg
moreeg
Posts: 842

I've mocked up this spreadsheet based on your data and reformatted it somewhat. As the data shows, Systolic can be in one category while diastolic can be in another. I've based the categorisation on the Diastolic but if it is more complex than that you'll need to define the categories more precisely.

Other things I've done is to colour code (using conditional formatting) each record based on the category
Colour coded the Average table based on the average diastolic rating
Assigned categories using Vlookup
Moved the averages to the top so that it is easier to add records to the bottom of the list
The categories table looks strange because of the way vlookup works but you can hide the table farther across the worksheet so that it isn't visible

If you want a copy of the spreadsheet let the mods know.

Posted 1 year ago
Top
 
moreeg
moreeg
Posts: 842

I missed the High/Low calcs - here is another attempt

Posted 1 year ago
Top
 
Se7ven
Posts: 10

The category chart is wrong in that you have no values entered for the Desirable part. My posting may have contributed to that. See link for chart

http://picpaste.com/6adccd7947.....0bfe20.JPG

I was inserting rows for each new reading above the high, low and average. But perhaps having it on top seems better. However, I prefer that everything is in one chart as I am linking this to a table in a Word file. As such how can one include the Occurrences in this one table?

Posted 1 year ago
Top
 
moreeg
moreeg
Posts: 842

Hi Se7ven

You're right about my chart being wrong but for different reasons. If you look at my screenshots I do have entries that show up as "Desirable". Also, the chart you posted isn't precise enough for what you are trying to do. Desirable shows as Diastolic 75 but what is it if it shows as 74 or 76. What is the range for desirable and what is the category if it is below that range. I've redone the ranges as follows.

The way Excel interprets this for Diastolic as
0 - 69 = Low
70 - 79 = Desirable
80 - 89 = PreHyper
90 - 99 = Hyper 1
>=100 = Hyper 2

Also - Systolic can be in one category and Diastolic can be in another (as it shows in my screen shots)- so which category should it be in?

Finally - how do you want it to look in Word? At some point you'll have so many daily entries you may not want to move that to Word and, instead, just show the summary information - or, at least, show the dailies separately as an appendix.

Give it some thought and let me know.

Posted 1 year ago
Top
 
Se7ven
Posts: 10

Ok. Those categories are from the Mayo clinic. Perhaps the desirable should also have a range as well. If so, than logic dictates that it be 75-79 for Diastolic and 115-119 for Systolic. Anything below that is undesirable as too low a blood pressure would put someone is serious peril of their life.

As to what I want displayed in Word, well the thought did enter my mind after my previous post but unsure how to deal with it. Maybe it would be better to display the running average and high-low occurrences in a single table if possible within Word. In addition to the following categories for Pulse readings:

http://picpaste.com/f94307c81c.....1ab534.JPG

Posted 1 year ago
Top
 
moreeg
moreeg
Posts: 842

Hi Se7ven

Okay - here's what I've done ...

added the pulse readings, occurrences and averages
re-done the categories to make the ranges clearer
taken out the conditional formatting because I'm not sure how you would want to send them

I'll send the Excel file to a mod who will contact you to see if you want it. If you do, you can play around with it - add all your records and then figure out any changes you might want - you can come back and ask how to make those changes.

Here's what I've done ...

Categories ....

Posted 1 year ago
Top
 
Lighthouse
Lighthouse
Posts: 13598

Se7ven. If you want the file, click on Moderator to the left of this post, and email me.

Accepted Answer · Posted 1 year ago
Top
 
Se7ven
Posts: 10

Thanks for the help.

Posted 1 year ago
Top
 
Se7ven
Posts: 10

I hope it's ok to reopen this thread and not have to start a new one. The reason for it being that I have been trying to determine how best to graph this data and so far not satisfied with the results. I was hoping to be able to see graphically using the line chart how the data changes over a specific time period. That is, doing a graph for monthly, weekly, daily, a.m., and p.m. periods. The problem I am having is that I am unable to change what appears along the horizontal x-axis. Perhaps I need an altogether different graphing tool than what comes with Excel.

Posted 1 year ago
Top
 
moreeg
moreeg
Posts: 842

Hi 7

Can you post a screen shot of what you have and then explain what you want? Normally it is quite simple to determine what is on the X and Y axes.

Posted 1 year ago
Top
 
Se7ven
Posts: 10

I don't have any screenshots to post because of the noted poor results I got. What I would like to see on the x-axis would include timelines. For example, days of the week (Monday, Tuesday ...); a.m. and p.m. hours ( Noon, 1:00, 2:00, 3:00, up to midnight and then from just after midnight to just before the noon hour) each having its own graph. Weeks of the month: 1, 2, 3, 4, 5. Months of the year, January, February... I may also want to include another for weeks of the year (1-52). Right now I don't have enough date compiled to produce the monthly chart.000

The Y-axis would have the BP Pressure & Pulse Readings ranging from 0 - 160, in increments of no more than 10. Don't know if one can produce a graph with increments of 1.

Also the graph that Excel produces is rather small in size which may contribute to how the y-axis is populated and shows.

Posted 1 year ago
Top
 
moreeg
moreeg
Posts: 842

Are you using the spreadsheet I prepared for you? If you are there is an example chart in Sheet5.

If you aren't using it I'd need to know how your data is arranged.

Posted 1 year ago
Top
 
Se7ven
Posts: 10

No I copied and pasted it to another sheet. Did not know that there was a Sheet 5 with a chart on it.

Posted 1 year ago
Top
 
moreeg
moreeg
Posts: 842

Hi 7

Here is a pretty good tutorial on building charts. You can google "creating charts in excel" or something similar to get more examples. If you give it a go and ask specific questions we can almost certainly help you out.

Moree

Posted 1 year ago
Top
 
Se7ven
Posts: 10

I found the original file Health.xlsx and I don't see a Sheet 5. I have looked at the other Sheets, reduced the zoom level to 25%, and none have a graph. Unless Health.xlsx is not the file that is being referenced.

Posted 1 year ago
Top
 
Se7ven
Posts: 10

The Youtube video is for Xcel 2007. Xcel 2003 does not have the features utilized in the tutorial.

Posted 1 year ago
Top
 



Topic Closed

This topic has been closed to new replies.

Get Free Articles in Your Inbox!

Join 134,000 newsletter readers

Email:

Go check your email!