anyone here with excel knowledge?

[Al.Neri]

I've been tinkering with microsoft excel, and I am trying to figure out how to do something.

I know how to set a cell to add up the total of a range of cells filled with digits, but I am trying to figure out how to assign a numerical value to a letter to do the same.

for example, if I enter the letter X into a cell, it automatically counts as the number 1, and likewise, if I enter a letter Y, it counts as a 0.

from there, I want to set my "total" cell to add up the total and output it as a numerical value based on the selected cells and their contents.

dunno if it makes sense, I always find these things harder to explain than it is to think about them.

anyone here know how to do this?

 

[LinearHD]

you could put you're supposed X and Y values into cells that are irrelevant to your overall spreadsheet (far away, so e.g. X1 and Y1) and then just reference to those cells when you need to input those values?

 

[Al.Neri]

I'm not really sure how that would achieve what I want it to do.

this is the sort of thing I am trying to figure out

I know how to get the total cell to add up the values of the other cells within the specific range, and I also know how to auto format it to colour the text/cell dependant on what is entered. what I am after, is a way of telling the total cells that in the cell range it is set to add up, that X=1 and Y=0, so it can total the content up the same way as it would if it was 1's and 0's..

 

[PC Fanatic]

If you are only using 1 and 0, you could use a COUNTIF function to count the number of X cells, which would give you a total.

E.g. If X always = 1, you could use COUNTIF to count the X cells, which would give you a total.

 

[Al.Neri]

I tried that, but it seems to me that I need to redo the action everytime I make an update.

what I am intending to do, is to update each row on a weekly basis, so the numbered columns refer to weeks, and the actual X & Y entries relate to the outcome of a yes/no event. the potential number of rows would make it impractical to use COUNTIF. I am after something that will update each total on the fly as I enter the relative data.

 

[SrgColman]

Perhaps using the IF function?

=IF(C8=1,"X","Y")

That way when C8 is 1 it will display X is not it will display Y

However this would require two sets of the table, one you could set to be hidden and enter the data into, while making the data appear as you would like formatted in the coloured table.

Change for all cells?