• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Develop an Excel formula using the WHO CVD Risk Chart 2019

Dr Lingkan

New Member
Can anyone develop two separate Excel formulas to calculate cardiovascular risk in an individual? One formula for 'People with diabetes' and another for "People without diabetes'. Here, I have added a chart for these two groups of population. Usually, using this chart I can manually calculate cardiovascular risk. But I want an Excel formula that will automatically calculate the risk. In the chart, each number indicates the risk which can be estimated/calculated using the information: gender (men/women), smoker/nonsmoker, age, systolic blood pressure in mmHg, & total cholesterol level. For example, if a woman has diabetes, is a nonsmoker, is 63 years of age, and has a systolic blood pressure of 140 mmHg & total cholesterol of 6.5 mmol/l, her cardiovascular risk will be 9%. I will manually check the accuracy of the formula using the attached risk chart.
 

Attachments

  • Lab_chart.pdf
    147.2 KB · Views: 8

Dr Lingkan

How did You get 9%? ... Isn't it 15?
Here is one formula for both cases...
Make needed selection with drop-downs and You'll see it.
... You can add those values there; that sheet is protected without password.
 

Attachments

  • Dr.xlsx
    14.5 KB · Views: 5
Last edited:

Dr Lingkan

How did You get 9%? ... Isn't it 15?
Here is one formula for both cases...
Make needed selection with drop-downs and You'll see it.
... You can add those values there; that sheet is protected without password.
Can't generate result. Is it possible to set equations for those with diabetes and those without?
 

Dr Lingkan

As written ...
... You can add those values there (from Your PDF); that sheet is protected without password.
After that, You can get Your %
Before that, You can get position of Your %.
Use selections (dropdowns) to select needed selection.
If You've selected 1st selection With diabetes and
after that You'll do selection Without diabetes.
You can see both results.
One people ...can ... or cannot ... have both in same time.
 

Dr Lingkan

As written ...
... You can add those values there (from Your PDF); that sheet is protected without password.
After that, You can get Your %
Before that, You can get position of Your %.
Use selections (dropdowns) to select needed selection.
If You've selected 1st selection With diabetes and
after that You'll do selection Without diabetes.
You can see both results.
One people ...can ... or cannot ... have both in same time.
I have a data set where with other data, age, sex, smoking use, blood pressure and blood cholesterol are recorded. I want to add a column of cardiovascular risk. I want an equation which I want to set in that column, so that the cardiovascular risk will automatically calculated without any manual input. The excel file you have added, I have to manually put the values to calculate risk.
 

Attachments

  • Book1.xlsx
    8.1 KB · Views: 5

Dr Lingkan

As I have written,
You have to apply those 1400 values to my sample file someway.
I have only possible to do that manually myself.
Even I would do it, You should verify each of those 1400 values manually.

After You have applied those 1400 values, I could do something as You would like to have in Your Book1.xlsx-file.

Please, next time - You should send that kind of Excel-file, which You would like to use at once.
 

Dr Lingkan

Here next sample based Your Book1.xlsx-file.
Note: There are two sample values in Data-sheet.
After You've added those 1400 real values to Data-sheet,
You'll able to use List-sheet to get Your expected results.
Both sheet has protected (without password).
 

Attachments

  • Dr.xlsx
    18.5 KB · Views: 6

Dr Lingkan

Here next sample based Your Book1.xlsx-file.
Note: There are two sample values in Data-sheet.
After You've added those 1400 real values to Data-sheet,
You'll able to use List-sheet to get Your expected results.
Both sheet has protected (without password).
I can't see the cell of Excel. It looks as attached. Sorry for the ignorance.1701006920641.png
 

Dr Lingkan

There is an another sheet named List as You've given.
Screenshot 2023-11-26 at 16.09.41.png
Note: Those right side values (99 & 98) are test values that I could verify that my formula works ( finds correct place ):
For Data-sheet, You Dr Lingkan should someway add those 1400 values from Your given Lab_chart.pdf.
( As I've written few times. )
After You have take care Data-sheet, You can start to use List-sheet as You've wished.
What is missing?
What cannot You see?
 
Last edited:
Can anyone develop two separate Excel formulas to calculate cardiovascular risk in an individual? One formula for 'People with diabetes' and another for "People without diabetes'. Here, I have added a chart for these two groups of population. Usually, using this chart I can manually calculate cardiovascular risk. But I want an Excel formula that will automatically calculate the risk. In the chart, each number indicates the risk which can be estimated/calculated using the information: gender (men/women), smoker/nonsmoker, age, systolic blood pressure in mmHg, & total cholesterol level. For example, if a woman has diabetes, is a nonsmoker, is 63 years of age, and has a systolic blood pressure of 140 mmHg & total cholesterol of 6.5 mmol/l, her cardiovascular risk will be 9%. I will manually check the accuracy of the formula using the attached risk chart.
Creating precise cardiovascular risk formulas requires a detailed understanding of the specific risk factors and their corresponding weights in the chart. Since I can't see the chart, I can guide you on creating a formula based on general principles.

Assuming you have columns for Gender, Diabetes Status, Smoking Status, Age, Systolic Blood Pressure, and Total Cholesterol, you could use nested IF statements with appropriate coefficients. Here's a simplified example:

For People with Diabetes:


=IF(AND(Gender="Woman", Diabetes="Yes", Smoking="No", Age>=50, SystolicBP>=140, Cholesterol>=6.5), 9, 0)


For People without Diabetes:


=IF(AND(Gender="Woman", Diabetes="No", Smoking="No", Age>=50, SystolicBP>=140, Cholesterol>=6.5), 5, 0)


This is a basic template. Adjust the conditions and coefficients based on the specifics of your chart. Be cautious with the age range, blood pressure, and cholesterol thresholds, as these might vary in actual risk assessment models.
 
Creating precise cardiovascular risk formulas requires a detailed understanding of the specific risk factors and their corresponding weights in the chart. Since I can't see the chart, I can guide you on creating a formula based on general principles.

Assuming you have columns for Gender, Diabetes Status, Smoking Status, Age, Systolic Blood Pressure, and Total Cholesterol, you could use nested IF statements with appropriate coefficients. Here's a simplified example:

For People with Diabetes:


=IF(AND(Gender="Woman", Diabetes="Yes", Smoking="No", Age>=50, SystolicBP>=140, Cholesterol>=6.5), 9, 0)


For People without Diabetes:


=IF(AND(Gender="Woman", Diabetes="No", Smoking="No", Age>=50, SystolicBP>=140, Cholesterol>=6.5), 5, 0)


This is a basic template. Adjust the conditions and coefficients based on the specifics of your chart. Be cautious with the age range, blood pressure, and cholesterol thresholds, as these might vary in actual risk assessment models.
Could you add an excel file with this formula?
 
It's far from easy to reverse engineer such tables to create a formula. I wouldn't like to do it; I'd search out an algorithm on the interweb first.
Instead, in the attached is a single table created on Sheet1 (with the help of a macro) from the pdf file where each of the 1400 values is on its own row, then that table is used to lookup the values in columns A to F of Sheet2 (formula in column G). So far, cursory checks have been OK.
 

Attachments

  • Chandoo55493_v2.xlsx
    56.3 KB · Views: 3

Dr Lingkan

Here is sample which will work will many Excel-versions.
You can verify/compare Your values with same kind of layout than Your given PDF-file.
 

Attachments

  • Dr.xlsx
    22.6 KB · Views: 4
Back
Top