Collarspace Discussion Forums


Home  Login  Search 

Excel Formula


View related threads: (in this forum | in all forums)

Logged in as: Guest
 
All Forums >> [Casual Banter] >> Off the Grid >> Excel Formula Page: [1] 2   next >   >>
Login
Message << Older Topic   Newer Topic >>
Excel Formula - 8/17/2006 11:09:32 AM   
LuckyAlbatross


Posts: 19224
Joined: 10/25/2005
Status: offline 		So I'm working with an Excel spreadsheet here and have to make a formula that's tripping me up, so I figured I'd do the CM thing AND figure out the formula at the same time.

We've got two columns of numbers, both prices.  Column A is last years prices and Column B is this years prices.  Some have gone up, some down, and some stayed the same.

I need a formula that will show in the Column C the percent change from the previous year.  So if the price was 5 dollars last year and 6 dollars this year, it was a 20% price increase.  But I'm not sure how to type up the formula for that.

Also, if there was a way to signify whether it was a percent change up or down automatically, that would rock.

_____________________________

Find stable partners, not a stable of partners.

"Sometimes my whore logic gets all fuzzy"- Californication
Profile   Post #: 1
RE: Excel Formula - 8/17/2006 11:15:00 AM   
LotusSong


Posts: 6334
Joined: 7/2/2006
From: Domme Emeritus
Status: offline 		Have colum B divide into column A to = C?

_____________________________

Life Lesson #1

I'm not your type.
I'm not inflatable.


(in reply to LuckyAlbatross)
Profile   Post #: 2
RE: Excel Formula - 8/17/2006 11:17:14 AM   
LuckyAlbatross


Posts: 19224
Joined: 10/25/2005
Status: offline
quote:

ORIGINAL: LotusSong
Have colum B divide into column A to = C?

That just gives you a divded price.  In my example, then you'd get 1.2.  You'd have to then multiply it by 100 to get the full percent increase (120%) and then subtract 100 from it to get the percent change (20% up).

I need a formula to do all that simply and hopefully even signify the percent change as positive or negative.

_____________________________

Find stable partners, not a stable of partners.

"Sometimes my whore logic gets all fuzzy"- Californication

(in reply to LotusSong)
Profile   Post #: 3
RE: Excel Formula - 8/17/2006 11:28:46 AM   
LadyEllen


Posts: 10931
Joined: 6/30/2006
From: Stourport-England
Status: offline 		Hi

There is a clever way to do it - but simpler is to have two columns, one showing the gain in currency terms per item and then to use that column to divide the other - if you format that result as a percentage you should get what you need

E

(in reply to LuckyAlbatross)
Profile   Post #: 4
RE: Excel Formula - 8/17/2006 11:38:56 AM   
happypervert


Posts: 2203
Joined: 5/11/2004
From: Scranton, PA
Status: offline 		=b2/a2-1

where column B is new price

and format these cells as %; can also format so negative is red

< Message edited by happypervert -- 8/17/2006 11:40:38 AM >

(in reply to LuckyAlbatross)
Profile   Post #: 5
RE: Excel Formula - 8/17/2006 11:51:43 AM   
LuckyAlbatross


Posts: 19224
Joined: 10/25/2005
Status: offline 		Wow you guys rock!  I got it!

_____________________________

Find stable partners, not a stable of partners.

"Sometimes my whore logic gets all fuzzy"- Californication

(in reply to happypervert)
Profile   Post #: 6
RE: Excel Formula - 8/17/2006 11:56:50 AM   
seeksfemslave


Posts: 4011
Joined: 6/16/2006
Status: offline 		Formula is.... ((B-A)/A)*100 )

A and B need to be declared as real numbers.
I do not know how to truncate the answer to say 2 decimal places
If you have a manual check "format of variables"

If the division is integer division ie 5/6 = 6/5 = 1 then it wont work.

So 6 to 5 gives ( (5-6)/6)*100 ) = -16.66666666666

< Message edited by seeksfemslave -- 8/17/2006 12:09:44 PM >

(in reply to happypervert)
Profile   Post #: 7
RE: Excel Formula - 8/17/2006 1:51:19 PM   
seeksfemslave


Posts: 4011
Joined: 6/16/2006
Status: offline 		should be  ( (  B-A ) / A ) * 100 ie one extra bracket removed....I hope.

< Message edited by seeksfemslave -- 8/17/2006 1:52:06 PM >

(in reply to seeksfemslave)
Profile   Post #: 8
RE: Excel Formula - 8/17/2006 1:55:39 PM   
LuckyAlbatross


Posts: 19224
Joined: 10/25/2005
Status: offline
quote:

ORIGINAL: seeksfemslave
should be  ( (  B-A ) / A ) * 100 ie one extra bracket removed....I hope.

LOL I had excel clean it up for me automatically.  You're correct and the formula is just what I needed!  Thanks.

_____________________________

Find stable partners, not a stable of partners.

"Sometimes my whore logic gets all fuzzy"- Californication

(in reply to seeksfemslave)
Profile   Post #: 9
RE: Excel Formula - 8/17/2006 2:56:29 PM   
happypervert


Posts: 2203
Joined: 5/11/2004
From: Scranton, PA
Status: offline
quote:

( (  B-A ) / A ) * 100

[geek alert ON]

( (  B-A ) / A ) * 100 = (B/A -A/A) * 100 = (B/A - 1) * 100

So the question is if she wants to show a "%" sign after each number or not. If so, just use Excel formatting feature and don't multiply by 100; otherwise, use any variant above

[geek alert OFF]



(in reply to seeksfemslave)
Profile   Post #: 10
RE: Excel Formula - 8/17/2006 3:45:16 PM   
seeksfemslave


Posts: 4011
Joined: 6/16/2006
Status: offline 		Only for telling a man he was wrong I got two luvverly black eyes....boom boom.

In computer speak ( ( B-A) /A ) does not equal B/A -A/A

It means calculate B-A then divide the result by A   he he he he he he he It only hurts me when I larf.

I'm surprised you were so bold since Lucky Albatross had already said that the formula worked.


(in reply to happypervert)
Profile   Post #: 11
RE: Excel Formula - 8/17/2006 3:49:58 PM   
KatyLied


Posts: 13029
Joined: 2/24/2005
From: Pennsylvania
Status: offline 		This thread has now officially turned super geeky!






_____________________________

“If you want to live a happy life, tie it to a goal, not to people or things.”
- Albert Einstein

(in reply to seeksfemslave)
Profile   Post #: 12
RE: Excel Formula - 8/17/2006 4:08:08 PM   
gooddogbenji


Posts: 5094
Joined: 11/15/2005
From: Toronto
Status: offline 		Okay, I have one that's a bit tougher, and I don't think it's even possible:

I want to do my staff timesheets on Excel.  I want it to look as follows:

Start   End presplit  Start post split  End   Total hours  Rate   Total $
                                                             (b-a)+(d-c)                e*f


The issue is that when someone works past midnight, excel sees it as a negative time, and doesn't display it.  I tried a lot of stuff, but the problem remains.  Any suggestions?

Yours,


benji

< Message edited by gooddogbenji -- 8/17/2006 4:21:24 PM >

_____________________________

Prevent global warming. Stop burning patchouli.

(in reply to KatyLied)
Profile   Post #: 13
RE: Excel Formula - 8/17/2006 4:21:05 PM   
seeksfemslave


Posts: 4011
Joined: 6/16/2006
Status: offline 		Sit benji..... never say impossible with regard to computors.
Define the problem more precisely

eg A = starttime b = endtime
Also what do you mean by endbeforesplit startaftersplit.  MUST BE PRECISE

You know the old computor saying Rubbish in Rubbish out

(in reply to gooddogbenji)
Profile   Post #: 14
RE: Excel Formula - 8/17/2006 4:22:21 PM   
LadyEllen


Posts: 10931
Joined: 6/30/2006
From: Stourport-England
Status: offline 		easy peasy lemon squeezy!

they start the next day (the next row, I assume) from midnight. If they then work twice in the same day -once from midnight to 0100hrs for example, and then from 1800 to 2400hrs - use another row and have two rows per day - indulge yourself, Excel has hundreds of rows after all.

E


(in reply to gooddogbenji)
Profile   Post #: 15
RE: Excel Formula - 8/17/2006 4:23:25 PM   
gooddogbenji


Posts: 5094
Joined: 11/15/2005
From: Toronto
Status: offline 		We work with split shifts in hospitality.  I suppose, for the sake of this problem, we could leave that off....   so just start time and end time.

A=Start time
B=End time
C=Total Hours
D=Rate of pay
E=Total wage

And hooray!  If it aint impossible, it would save me a shitload of work......

Yours,


benji

_____________________________

Prevent global warming. Stop burning patchouli.

(in reply to seeksfemslave)
Profile   Post #: 16
RE: Excel Formula - 8/17/2006 4:23:44 PM   
LadyEllen


Posts: 10931
Joined: 6/30/2006
From: Stourport-England
Status: offline 		my goodness these fora are boring tonight if this is the best thread I can find........

(in reply to LadyEllen)
Profile   Post #: 17
RE: Excel Formula - 8/17/2006 4:27:39 PM   
gooddogbenji


Posts: 5094
Joined: 11/15/2005
From: Toronto
Status: offline
quote:

ORIGINAL: LadyEllen

easy peasy lemon squeezy!

they start the next day (the next row, I assume) from midnight. If they then work twice in the same day -once from midnight to 0100hrs for example, and then from 1800 to 2400hrs - use another row and have two rows per day - indulge yourself, Excel has hundreds of rows after all.

E



Doesn't work.  Excel uses 00:00 as midnight, so if I enter that they worked till midnight, that already causes the problem.  I could make it till 23:00, then from 00:00 to 02:00, and add one manually, but that's the same amount of work as I do now.

One thing I did try was doing an If then thing - if b<a, use a different formula, namely a-b(giving me the number of hours they didn't work)-24, but it won't even recognize the "24."

Yours,


benji

_____________________________

Prevent global warming. Stop burning patchouli.

(in reply to LadyEllen)
Profile   Post #: 18
RE: Excel Formula - 8/17/2006 4:39:19 PM   
LadyEllen


Posts: 10931
Joined: 6/30/2006
From: Stourport-England
Status: offline 		its half past midnight here now, but it looks like I have a project for tomorrow morning now...... thanks a lot Benji! LOL!

E 

(in reply to gooddogbenji)
Profile   Post #: 19
RE: Excel Formula - 8/17/2006 4:54:39 PM   
seeksfemslave


Posts: 4011
Joined: 6/16/2006
Status: offline 		have lost post due to timeout so I want to be quick this time.

Assuming 24 hour clock you can find out whether hours cross midnight by saying symbolically

if finishtime is less than start time
then
totalshifthours = 24 - starttime + finishtime
else
totalshifthours = finishtime - starttime.
endif

some manipulation of time input may be required to get say 20:45 time = 20.75 to the computer

(in reply to LadyEllen)
Profile   Post #: 20
Page:   [1] 2   next >   >>
All Forums >> [Casual Banter] >> Off the Grid >> Excel Formula Page: [1] 2   next >   >>
Jump to:





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts




Collarchat.com © 2025
Terms of Service Privacy Policy Spam Policy

0.203