Format as function of results

**Brokovich** · 03-02-2007, 11:17 AM

Hi guys

I have come across an interesting problem. I am using the sumif formula to sum values that are input by a third party.

A

B

Apple[IND £5[/INDENT]Pear[INDE £6[/INDENT]Apple[IND £4[/INDENT]Pear[INDE £10[/INDENT]
In column B people can either enter a monetry amount or they can enter a percentage. They have to enter the amounts consistently so the list will only ever contain either £ or %.

If i use the formula

=SUMIF(A2:A6,"Apple",B2:B6)

it works great when people enter the amounts with the £ but when people use the % the end result is 0.09 or similar. I want the formula to either end with 10 for currency or 2% for pecentage.

Can anyone help?

Thanks!

Brokovich

**oldchippy** · 03-02-2007, 11:34 AM

I'ts not exactly clear from the post, but a cell can only be formatted once, either to monetary or percentage

**Brokovich** · 03-02-2007, 11:49 AM

Thanks Old Chippy

Sorry about the garbled post, the formatting went a bit crazy.

Basically I want to know if it is possible for the formula to insert a £ before the result if the amounts entered are in £'s, and a % after the result if the amounts entered are in %. Also, if it is in % then it would need to move the decimal place up two...

Do you know if this is possible?

**oldchippy** · 03-02-2007, 11:54 AM

When you say values that are input by a third party presumably in column B, these cells must be formatted to either currency or percentage, it can't be both as all they are entering is numbers

**Brokovich** · 03-02-2007, 12:02 PM

The cells are currently just formatted as general, so if they enter 10 it is taken to mean £10. If they want it to be a percentage then they have to actually enter the % sign themselves, ie 2%.

**oldchippy** · 03-02-2007, 12:13 PM

To get a total, ALL the values in column B must be currency OR percentages, just can't total a mixure of both

**Carim** · 03-02-2007, 12:14 PM

Oldchippy is right ...

Unless there is somewhere an indication of which format is to be applied, a potential event macro will never solve the issue ...

**Brokovich** · 03-02-2007, 12:27 PM

Hi guys

that's right, there will only ever be one type in the list. This is a form people use to submit figures to me. Some people enter it as a number, others as a percentage, but any one list will only ever have either numbers OR percenatges. not both.

**Carim** · 03-02-2007, 12:32 PM

Could you upload a zipped copy of your worksheet ...?

**Brokovich** · 03-06-2007, 08:30 AM

I have attached a zipped copy of the sheet as an example.

In cell C18 I want the formula result to show whether it has summed a percentage or not. in the range (D6:D11) the client will only ever enter either a currency amount or a percentage, it will never be a mixture of the two.

Thanks again for your help!

B