Stumped

Blackspear · Apr 27th, 2002, 04:45 AM

Last week I sold $110 (A1), this week I sold $77 (B1) the percentage increase/decrease (C1) is -30%, what formula do I need to place in C1?

Aladin Akyurek · Apr 27th, 2002, 04:53 AM

In C1 enter:

=(B1-A1)/A1

Format C1 as %.

Blackspear · Apr 27th, 2002, 05:00 AM

Thanks for your reply, that doesn't work, it gives 43%, were the answer is a decrease of -30% in sales, $110 last week, this week down by 30% to $77

Tom Schreiner · Apr 27th, 2002, 05:21 AM

=(B1/A1)-1

Tom

[ This Message was edited by: TsTom on 2002-04-27 04:22 ]

Aladin Akyurek · Apr 27th, 2002, 05:22 AM

Quote:

On 2002-04-27 04:00, Blackspear wrote:
Thanks for your reply, that doesn't work, it gives 43%, were the answer is a decrease of -30% in sales, $110 last week, this week down by 30% to $77

=(B1-A1)/A1 in C1 gives me -30%, when A1 houses 110 and B1 77.

Blackspear · Apr 27th, 2002, 05:44 AM

My appologies Aladin, I don't know what I did, but your formula works, thank you very much. I have one more question for you. I am calculating 100/10 = 10, if 100/0 = 0, how do I get it to display "0" rather than the error code "Num/0!" ???

Tom Schreiner · Apr 27th, 2002, 06:23 AM

Aladin has left the building...

=IF(ISERROR(100/0),0,100/0)

Tom

Aladin Akyurek · Apr 27th, 2002, 06:42 AM

Quote:

On 2002-04-27 04:44, Blackspear wrote:
My appologies Aladin, I don't know what I did, but your formula works, thank you very much. I have one more question for you. I am calculating 100/10 = 10, if 100/0 = 0, how do I get it to display "0" rather than the error code "Num/0!" ???

If you want to avoid the #DIV/0! error, you can check the divisor as in:

=IF(E2,E1/E2,0)

When E1 houses 100 and E2 0 (or E2 is empty), this formula will return 0.

The condition of the IF function in the above formula for any number other than 0 is TRUE, otherwise FALSE. When the condition evaluates to TRUE than the divison E1/E2 is performed, otherwise 0 is returned.

Aladin

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 27th, 2002, 04:45 AM	#1
Blackspear New Member Join Date: Apr 2002 Location: Gold Coast, Queensland, Australia Posts: 23	Last week I sold $110 (A1), this week I sold $77 (B1) the percentage increase/decrease (C1) is -30%, what formula do I need to place in C1?

Apr 27th, 2002, 04:53 AM	#2
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,317	In C1 enter: =(B1-A1)/A1 Format C1 as %.

Apr 27th, 2002, 05:00 AM	#3
Blackspear New Member Join Date: Apr 2002 Location: Gold Coast, Queensland, Australia Posts: 23	Thanks for your reply, that doesn't work, it gives 43%, were the answer is a decrease of -30% in sales, $110 last week, this week down by 30% to $77

Apr 27th, 2002, 05:21 AM	#4
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	=(B1/A1)-1 Tom [ This Message was edited by: TsTom on 2002-04-27 04:22 ]

Apr 27th, 2002, 05:44 AM	#6
Blackspear New Member Join Date: Apr 2002 Location: Gold Coast, Queensland, Australia Posts: 23	My appologies Aladin, I don't know what I did, but your formula works, thank you very much. I have one more question for you. I am calculating 100/10 = 10, if 100/0 = 0, how do I get it to display "0" rather than the error code "Num/0!" ???

Apr 27th, 2002, 06:23 AM	#7
Tom Schreiner Board Regular Join Date: Mar 2002 Location: Cincinnati, Ohio, USA Posts: 6,824	Aladin has left the building... =IF(ISERROR(100/0),0,100/0) Tom