excel formula, is it possible to.....

[Smee]

hi, not too sure if what I've been asked to do is possible but what's new there!

what formula would I use to find out if a row of cells have a "Y" in them, if they do, autosum them and divide by 7? If they don't have a "Y" they are to be ignored.

My excel is a little hit and miss I'm afraid.

thanks for any help

[DJ-1701]

Should be possible, is it the number of Y's you wish to total and divide by 7, or another value in the same row?

[RichCowell]

Will the cells needed to be counted just have a Y in them, or will they have other characters too?

 

If it's just Y in them it'll be as follows,

 

=COUNTIF(4:4, "y")/7

 

Changing 4 to whichever row you're counting them in

Edited February 25, 2016 by RichCowell

[Seb1780]

hi, not too sure if what I've been asked to do is possible but what's new there!

what formula would I use to find out if a row of cells have a "Y" in them, if they do, autosum them and divide by 7? If they don't have a "Y" they are to be ignored.

My excel is a little hit and miss I'm afraid.

thanks for any help

 

If you're looking in a particular cell for the "Y" then the formula could be:-

 

if(cellref="Y",sum(range)/7,"")

 

Where cellref refers to the cell where you'd expect to see the "Y" and range is the row of cells that you want summed.

[DJ-1701]

There is also this method for searching for any Y, upper or lower in a string of text.

 

Edited February 25, 2016 by DJ-1701

[Smee]

The spreadsheet is set up with a "individual progress" row, this shows the progress over the school life of the pupil collated from other sheets of the same book to give a total, (='Year 1'!C17+'Year 2'!C21+'Year 3'!C23+'Year 4'!C24+'Year 5'!C25+'Year 6'!C25-'Year 6'!C300) this is the formula they have used.

They then want to have any of the "individual progress" boxes that have a "Y" in the column to add together and /7, I think its so that when they make changes (like adding a Y) the individual progress updates and the overal progress of the year group changes.

sorry if this is confusing, its done my head in just typing it.

[Seb1780]

Nope, not following that one!

 

Can you PM me a redacted example and I'll have a look.

[Smee]

Thank you for all your suggestions and offers of help. I finally figured it out (I think!)

=SUMIF(C5:AD5,"Y",C40:AD40)/7

cheers