Assigning a # value to a letter in Excel 2007

[MickG]

I really need to do an excle course but until then, can anyone talk me through how I assign different number values to letters in a colum or row. I think I need to use a "countif" formula.

Essentially I am recording customer sources of business approx 50 entries p/day and will have approx 10-15 sources of business which I intended to assign an individual letter for (example: newspaper ad = N or n, radio ad = R or r) and then at the end of each day, I want another box on my spreadsheet to show me how many "N"s or "R"s i have had for the day and so on and so forth. I can then graph the totals boxes easily.

Thanks in advance, Mick

[foz.in.oz]

Countif sounds like the way to go.

Click on the little fx next to the formula entry bar at the top left of the screen and select countif. This will then show a dialog box on the required arguments.

[p38arover]

Yes, as previously stated, COUNTIF will work

Assuming your data is in column A, rows 1 to 10, this is what you'd have to enter to count the no. of instances of the letter R. Put this formula elsewhere in the spreadsheet.

=COUNTIF(A1:A10,"r")


You'll need one instance of the formula for each letter to be counted.

[stikman]

Hey Mick,

Just call your 'helpdesk' if you need help :D

Speak Soon. :angel:

[MickG]

I really need to do an excle course but until then, can anyone talk me through how I assign different number values to letters in a colum or row. I think I need to use a "countif" formula.

Essentially I am recording customer sources of business approx 50 entries p/day and will have approx 10-15 sources of business which I intended to assign an individual letter for (example: newspaper ad = N or n, radio ad = R or r) and then at the end of each day, I want another box on my spreadsheet to show me how many "N"s or "R"s i have had for the day and so on and so forth. I can then graph the totals boxes easily.

Thanks in advance, Mick


Hey Mick,

Just call your 'helpdesk' if you need help :D

Speak Soon. :angel:

:DYeah was going to but you'll start charging me soon:D:D Both foz.in.oz and P38arover have sorted me out.....wish I knew about this gem a few spreadsheets back:mad: I really should do a course soon but hey, AULRO always comes through with the goods when you need it.

Thanks again guys........rest assured there will be plenty more Excel questions to come in the future:D:D:(

Aye, Mick

[dmdigital]

Yes, as previously stated, COUNTIF will work

Assuming your data is in column A, rows 1 to 10, this is what you'd have to enter to count the no. of instances of the letter R. Put this formula elsewhere in the spreadsheet.

=COUNTIF(A1:A10,"r")


You'll need one instance of the formula for each letter to be counted.

Change the formula of Ron's to read

=COUNTIF(UPPER(A1:A10),"R")

Should work and translates all letters to uppercase. I haven't got Excel at home any more so can't test it.

[p38arover]

The data doesn't need conversion to uppercase for the function to work. I tested it.

=COUNTIF(A1:A10,"r") works with upper or lower case data.

Regrettably, COUNTIF(UPPER(A1:A10),"R") doesn't work. I suspect it's because you can't use the UPPER function on a range.

[dmdigital]

The data doesn't need conversion to uppercase for the function to work. I tested it.

=COUNTIF(A1:A10,"r") works with upper or lower case data.

Regrettably, COUNTIF(UPPER(A1:A10),"R") doesn't work. I suspect it's because you can't use the UPPER function on a range.

Thanks Ron. I think I must have known this, but like I said, couldn't test it. Did realise COUNTIF wasn't case dependant.

[MickG]

Thanks again guys, yes tested with upper and lower case and formula works perfectly.:D

Appreciate everyone's time and input, Mick

[MickG]

Next question:angel:

How do I create a rule so that when a particular cell (calculating %) goes over say 25% for example, the figure displayed in said cell turns red or is highlighted in some way? I would also like to apply this rule to a cell calculating $ so when the cell calculates greater than a certain number it displays in red or is highlighted in some way?

Thanks again in advance;) Mick

Edit: Or, how do I add a permanent horizontal line on a graph (floor or ceiling limit if you like) so that when my figures are graphed I have an instant visual of a value being over or under floor or ceiling limit respectively?

[foz.in.oz]

Select the cell that you want to configure and then goto the FORMAT menu and select CONDITIONAL FORMATTING. A dialog box will appear that allows you to set upto three rules for the data. Have a play and if you need more help just ask.

[MickG]

Select the cell that you want to configure and then goto the FORMAT menu and select CONDITIONAL FORMATTING. A dialog box will appear that allows you to set upto three rules for the data. Have a play and if you need more help just ask.

What a great tool...........I will be here some time applying some of these tricks to a few of my spreadsheets:D Thanks again foz.in.oz;)