75ZT Community

raistlin · Posted 22 Nov 2013, 19:22 #1

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 · Posted 22 Nov 2013, 20:28 #2

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 · Posted 22 Nov 2013, 20:35 #3

Thanks Mike - all fixed

Dave · Posted 28 Nov 2013, 13:24 #4

An Excel question and I missed it

Darn!

takestock · Posted 28 Nov 2013, 17:23 #5

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 · Posted 28 Nov 2013, 19:01 #6

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 · Posted 28 Nov 2013, 19:58 #7

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

Fancy another? :hail:

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 · Posted 28 Nov 2013, 23:22 #8

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 · Posted 29 Nov 2013, 08:41 #9

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 · Posted 29 Nov 2013, 09:14 #10

Sorted!

My pleasure!

75ZT Community

75ZT Community

Excel spreadsheet modification by raistlin