75ZT Community

[Raistlin]

I have an Excel spreadsheet for household income / expenditure and it is just about perfect for me.

Except, having originated in the USA, it has $ signs where there should be £ signs.

Can somebody explain to me how to change these over without wrecking the spreadsheet please?

[Bermudan 75]

Hi Paul,

On the toolbar there is a tab General, click on that to accounting number format and you can change the symbol to £.

Cheers

Mike

[Raistlin]

Thanks Mike - all fixed

[Dave]

An Excel question and I missed it Darn!

[takestock]

So as not to disappoint then: Excel 2007 sheet
I have a column of figures and the next column is populated with the letter P or F At the bottom of the column I want the column of figures to total the figures that are next to an F but ignore the figure if the letter in the adjacent column is P

[Dave]

The messy method would be to put in a "helper" column, to filter out the numbers. As an example (assuming data in range A1 to A10, and letters P and F in B1 to B10) you could put this in C1

=IF(B1="F",A1,"")

and then drag it down to C10, finally doing your calculation on range C1:C10 and then hiding the Column

However, luckily there's a neater solution, again assuming A1:A10 for data, B1:B10 for letters, and that's to stick this formula anywhere you like!

=SUMIF(B1:B10,"F",A1:A10)

Hope that helps!

[takestock]

Coooo it works. You have no idea how long a novice like me has been looking at conditional formatting

Fancy another?

If the number in the cell is 0 I need it to make a different cell a different colour (green) with the word Pass.
If the number is between 1 & 10 inclusive it needs to be amber and the word PWM
if the number is 11 or over in needs to go red and say Fail

I like this game

[Dave]

Assuming you have the values dropping down in A1, put the following into B1.

=IF(A1=0,"Pass",IF(AND(A1>=1,A1<=10),"PWM",IF(A1=11,"Fail")))

Then use Conditional Formatting to colour B1

Cell Value Is
Equal to
="Pass"

Repeat this for "PWM" and "Fail"

Then drag the formula down as far as needed

[takestock]

Genius It actually needs to fail at anything 11 and over so I have added > in before =11 and all is well.
Cannot thank you enough

[Dave]

Sorted!

My pleasure!