Excel Formula (Full Version)

All Forums >> [Casual Banter] >> Off the Grid



Message

LuckyAlbatross -> Excel Formula (8/17/2006 11:09:32 AM)

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.



LotusSong -> RE: Excel Formula (8/17/2006 11:15:00 AM)

Have colum B divide into column A to = C?



LuckyAlbatross -> RE: Excel Formula (8/17/2006 11:17:14 AM)

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.



LadyEllen -> RE: Excel Formula (8/17/2006 11:28:46 AM)

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



happypervert -> RE: Excel Formula (8/17/2006 11:38:56 AM)

=b2/a2-1

where column B is new price

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



LuckyAlbatross -> RE: Excel Formula (8/17/2006 11:51:43 AM)

Wow you guys rock!  I got it!



seeksfemslave -> RE: Excel Formula (8/17/2006 11:56:50 AM)

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



seeksfemslave -> RE: Excel Formula (8/17/2006 1:51:19 PM)

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



LuckyAlbatross -> RE: Excel Formula (8/17/2006 1:55:39 PM)

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.



happypervert -> RE: Excel Formula (8/17/2006 2:56:29 PM)

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]





seeksfemslave -> RE: Excel Formula (8/17/2006 3:45:16 PM)

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.




KatyLied -> RE: Excel Formula (8/17/2006 3:49:58 PM)

This thread has now officially turned super geeky!

[8D]





gooddogbenji -> RE: Excel Formula (8/17/2006 4:08:08 PM)

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



seeksfemslave -> RE: Excel Formula (8/17/2006 4:21:05 PM)

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



LadyEllen -> RE: Excel Formula (8/17/2006 4:22:21 PM)

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




gooddogbenji -> RE: Excel Formula (8/17/2006 4:23:25 PM)

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



LadyEllen -> RE: Excel Formula (8/17/2006 4:23:44 PM)

my goodness these fora are boring tonight if this is the best thread I can find........



gooddogbenji -> RE: Excel Formula (8/17/2006 4:27:39 PM)

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



LadyEllen -> RE: Excel Formula (8/17/2006 4:39:19 PM)

its half past midnight here now, but it looks like I have a project for tomorrow morning now...... thanks a lot Benji! LOL!

E 



seeksfemslave -> RE: Excel Formula (8/17/2006 4:54:39 PM)

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



Page: [1] 2   next >   >>

Valid CSS!




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