Spreadsheet maths help!

I have a spreadsheet I am trying to make. I am looking for a equation to work out a percentage. Without going into too much detail I have a row and 1s to represent something and 0s to represent something else and a sum at the end of that row. I want the percentage to represent the total amount of 1s in comparison to the number of cells going across. For example five 1s and five 0s with a total of ten columns i was hoping would give a percentage of 50%. I’m going to have lots of different rows the same and I need to compare the overall number of 1s for each row. Just wondering if this is possible. Maths isn’t my strong point at all!

Thanks!

Reply 1

MindMax2000

Universities Forum Helper

21

Original post by Chloe1173619

I have a spreadsheet I am trying to make. I am looking for a equation to work out a percentage. Without going into too much detail I have a row and 1s to represent something and 0s to represent something else and a sum at the end of that row. I want the percentage to represent the total amount of 1s in comparison to the number of cells going across. For example five 1s and five 0s with a total of ten columns i was hoping would give a percentage of 50%. I’m going to have lots of different rows the same and I need to compare the overall number of 1s for each row. Just wondering if this is possible. Maths isn’t my strong point at all!

Thanks!

Sounds unncessarily complicated.

Nevertheless you're looking at using SUMIFs or IF and SUM formulas (the former being a combination of the other 2, which makes it easier).

I don't think I can give you further hints than this otherwise it might possibly breach TSR guidelines.

Reply 2

davros

16

Original post by Chloe1173619

I have a spreadsheet I am trying to make. I am looking for a equation to work out a percentage. Without going into too much detail I have a row and 1s to represent something and 0s to represent something else and a sum at the end of that row. I want the percentage to represent the total amount of 1s in comparison to the number of cells going across. For example five 1s and five 0s with a total of ten columns i was hoping would give a percentage of 50%. I’m going to have lots of different rows the same and I need to compare the overall number of 1s for each row. Just wondering if this is possible. Maths isn’t my strong point at all!

Thanks!

as above, in general functions like SUMIF/SUMIFS/COUNTIF/COUNTIFS will help you sum or count entries in a row or column that belong to certain categories.

However, in this case things are a lot simpler because you have only 2 categories and you are using 1 and 0 to identify them. I would personally just use the SUM function on the row to get the number of 1s because if you have 0s and 1s in cells A1 to A10 then adding up the numbers in that range will tell you the number of 1s automatically - if you have 3 1s and 7 0s then the sum will be 3 and if you have 6 1s and 4 0s then the sum will be 6. So the function SUM(A1:A10) tells you the number of 1s. You haven't said whether there are a fixed number of columns in each row, so you could either subtract the number of 1s from the (fixed) number of columns to get the number of 0s, or use a COUNT function to count how many numeric entries there are in total in each row and then deduce the number of 0s using this. All this assumes that you are careful with data entry and you only ever have 0s and 1s (or empty cells) in the range.

(I spent many years getting paid to analyse things on spreadsheets and there will be more ways to solve this problem than you can think of, but from what you've posted I think the above is the simplest solution :smile: