Mathematics Problem

[taichiperson]

I'm in my mid fifties, and the furthest I got in Maths at school was O'level, which I just scraped past. I understand the principals of simple Maths, but I think I have mild discalculia, as I can never fix numbers in my head once I get past tens and units. I'm self employed and use a spreadsheet for my finances. I'd like to use some formulas, but don't even know how to begin beyond the simplest ones. I don't even know what branch of Maths I need to use.

The formula I'm stuck on goes like this; I run exercise classes in towns and villages nearby, but of course the classes don't run 52 weeks of the year. I need a formula that will count the number of weeks out of 52 that the class ran so that I can calculate mileage and room hire etc without having to physically run down the column with my finger, counting the number of classes.

I'd be grateful if someone could tell me what formula to use, and point me to the right branch of mathematics to find out for myself what the process behind the formula is - is it logic? algebra?

Thanks in advance

[Old_Geezer]

My first thoughts are that you can only really use a set formula if the circumstances do not change. If you have 452 weeks in a year and yet leave one out every now and again, then somehow the formula needs to pick up the missing ones, which are presumably fairly random.

Thinking about spreadsheets, which you mention, then if there is still a row for each week but an empty cell when a class did not run then I'm fairly sure one can count non-empty cells and maybe use that as a multiplier for whatever the cost of a class is.

pologies I don't know these things in detail off the top of my head and am a little busy at present, but I think the help function for the spreadsheet should give a formula for counting cells with something in them in a particular column or cell selection.

[Old_Geezer]

52 weeks, like I %&^$%^$(* typed >:-(

[Old_Geezer]

Apologies, like I %&^$%^$(* typed >:-(

[factor-fiction]

You could use 'if' statements but maybe it's just easier to have anther column in which you put a 0 or a 1 depending on whether a session was held and then you can sum these

[nannybooby]

why don't you do an on line course for excel they cover all these topics and more , also you can print out the course to keep for future ref.The cost of the course is also tax deductible.

[pixie373]

Wouldn't it be easier to just make a tally of when the class doesn't run?

[jim360]

If I understand the problem correctly there isn't a formula because the number of weeks the class doesn't run is arbitrary. However if you have a spreadsheet there may be a way to get around this. That will very much depend on the information in the spreadsheet, and how it's being presented. We'd need to know that before being able to proceed further.

What would probably needed is a sort of "IF" formula that would work along the lines of:

"=IF([class took place that week],1,0)"

This formula will output 1 if the class took place that week, and 0 if it did not. You can then go to the bottom of the column (or end of the row), and enter a formula that would be:

"=SUM([column])"

This would add up all the 1's and 0's together, and the answer would be exactly the number of weeks in which the class took place.

The starting point in this is how you tell excel whether or not the class took place. A cell that simply states "YES" if it happened, and "NO" if it did not, would do, and then the if formula would become:

=IF(C1="YES",1,0)

(assuming you've put the Yes or No in cell C1).

I hope this helps.

[Prudie]

I think factor's solution of 1s and 0s and then just highlight the column and sum is far simpler than trying to use formulae and IFs etc in Excel. It's what I was going to suggest until I saw he had already.

[jim360]

It's probably simpler, yes. That post of mine was sort of a train of thought, really. That said, using YES and NO has the advantage of being more visual and wordy which may or may not be more helpful.

Anyway, the solution is one way or another to create a column of 1's and 0's where 1 means that the class took place that week and 0 means it didn't, and then use =SUM([first cell]:[last cell]) to add them all up. Whether you generate 1 or 0 by an IF formula or not is up to you.

[Aquariel]

Further to jim360's answer, if you have a column in your spreadsheet that has, for example, "YES" when the class was on, you can use a COUNTIF formula instead:

COUNTIF(A1:A20,"YES") - that will count all the cells with YES in, and saves having to create an extra "working out" column, and is easier to follow (I think).

[taichiperson]

Thanks all. My elder son did help one year, and I think he made an extra hidden column with 1's and 0's in - but he's a bit rusty and couldn't reproduce it. I understand that the formulas with IF in it might be useful - what type of maths is that? I was also thinking that on the weeks the class doesn't run, there will be a zero value as I won't have taken any money. So what formula can I use to count the zeros?

[jim360]

The "IF" equation is a logical test. Is a statement true or not? If Yes, output 1; if No, output 0.

To answer your other question, you needn't count the zeros as well. However you set up the 1's and 0's, or if you use countif, then to count the zeros you can just subtract the answer from 52. The formula for this would be "=52-[cell with the number of weeks you did play]".

[Old_Geezer]

I suspect I was thinking of something like :

=COUNTA(A2:A8)

but if you check the help file for your spreadsheet's functions there is a wealth of info there.

[ToraToraTora]

in excell just put a 1 in a column at the end for each line you have then total that column. You can then hide the column, (put the total in another column)

[taichiperson]

Thanks all - I discovered how to use COUNTIF - some of the values were constant, in which case I used something like COUNTIF(,25) and some values varied, in which case I used

[taichiperson]

Sorry - post got cut off somehow - it continues

[taichiperson]

Thanks all - I discovered how to use COUNTIF - some of the values were constant, in which case I used something like COUNTIF(,25) and some values varied, in which case I used

[taichiperson]

aargh sorry, no idea why my posts are getting cut off.

I also used "

[taichiperson]

Thanks all - I discovered how to use COUNTIF - some of the values were constant, in which case I used something like COUNTIF(,25) and some values varied, in which case I used < 0. I'm pleased that I worked how to do it rather than someone do it for me and not be able to adapt it. I have a nice neat spreadsheet now.

[taichiperson]

Typing the formula obviously messes up my posts, so I can't reproduce exactly what I used. Suffice to say that I worked out how to do it, with your help, thanks :)