Excel % formula

New Topic
This topic has been archived, and won't accept reply postings.

My mate James says this is a shit formula;

=SUM(A1-(A1*B1))

it is for working out % . 
A1 =£?

B1= %
Please discuss and provide a better formula/ method, if there is one? 
Please be kind as he aint that bright, he’s a civil servant and calls the Peak the Peaks. Don’t  understand the Peaks / Peak thing, bit weird?

He is a Vegie to boot.  He climbed VS once and comes from Wales, great climbing. He has been known to combine coke with Ket, never helps.

Thoughts please.

cheers


 

3
 Blue Straggler 04 Mar 2021
In reply to A Nidderdale boulderer.:

are you James? 

 Michael Hood 04 Mar 2021
In reply to A Nidderdale boulderer.:

It's not clear where the SUM bit comes into it because in an individual cell your formula is equivalent to

=A1-(A1*B1)

 Route Adjuster 04 Mar 2021
In reply to Michael Hood:

The SUM usage is a common mistake (I think it might be taught at school) - people think that a calculation is doing a "sum" so they put everything in a SUM( ) function.  Infuriating but I can see why it happens.

Not sure how it works out %, if B is a % then the following are results

Assume A = 50 and B = 10%

50 - (50*10) = -450

Do you mean that B should be % of A so the answer should be

50 - (50*0.1) = 45 This means that the formula would calculate the % off a starting number, is this what James wants, if so then it works fine.  the other way to do the same thing would be to write the formula as

= A1*(1 - B1)

Assuming A = 50 and B = 10% then we get the result below

50 * (1-0.1) = 50 * 0.9, which = 45.

 Rob Exile Ward 04 Mar 2021
In reply to A Nidderdale boulderer.:

Tell me what they are trying to calculate and I will tell you the formula.

I agree that the use of sum() is unnecessary, it's a bit depressing because using a spreadsheet should be a core skill and anyone who uses sum to define a formula has misunderstood the very basics.

 nufkin 04 Mar 2021
In reply to Rob Exile Ward:

>  using a spreadsheet should be a core skill and anyone who uses sum to define a formula has misunderstood the very basics.

This was brought home to me when I was trying to work out how to make a formula that would show what the VAT portion was for a given total. It seemed like it should be simple, but I discovered that the Excel course I'd done at uni gave more consideration to formatting the spreadsheet to look nice than to explaining what formulae are and how they work

 marsbar 04 Mar 2021
In reply to A Nidderdale boulderer.:

Sum is for adding up only. 

Are you trying to find percentage 

Like 23 out of 60 people? 

So if 23 is in a1 and 60 is in b1

=(a1/b1)*100  should give you the answer.

Edit

Just realised you want percentage of amount. Will leave the other above in case its useful another time.

So money is in a1 and the percentage is in b1

You need to multiply them and divide by 100. 

=(a1*b1)/100

Or you can multiply by 0.01 instead of dividing by 100 which is probably easier 

=a1*b1*0.01

I know it looks weird starting with an = sigh but that is how excel does it.  It means whatever goes in the cell you clicked in is equal to what you type. 

Post edited at 10:06
 marsbar 04 Mar 2021
In reply to A Nidderdale boulderer.:

Do you know how to copy the formula down using the magic black cross at the bottom right of the cell?  There is a black blob at the bottom right of each cell when you click on it.  Put your mouse there and it changes to a black cross. 

Use the black cross to drag your formula down the column. 

https://contexturesblog.com/archives/2011/08/31/quickly-copy-excel-formula-...

 marsbar 04 Mar 2021
In reply to A Nidderdale boulderer.:

are you deliberately trying to subtract percentages? 

If you are then I need more information of what you are trying to do.  

 Rob Exile Ward 04 Mar 2021
In reply to nufkin:

Just a quick question - how many people posting here understand could explain the use of a $ sign in a formula?

I'm not getting at anyone or trying to be a smartarse, I'm just intrigued really...

 Martin W 04 Mar 2021
In reply to Rob Exile Ward:

I do.  In fact I'd say that if you don't know what it's for then that's another bit of fundamental Excel knowledge you're missing.

 Cobra_Head 04 Mar 2021
In reply to Rob Exile Ward:

> Just a quick question - how many people posting here understand could explain the use of a $ sign in a formula?

> I'm not getting at anyone or trying to be a smartarse, I'm just intrigued really...


Why would you need to know that, or is that just a supplementary bonus question?

I do know by the way.

 Rob Exile Ward 04 Mar 2021
In reply to Cobra_Head:

I'm just intrigued. I've been teaching that stuff since 1981 (it was part of Multiplan, the very first Microsoft spreadsheet) yet my kids - with two business degrees and 2 years of a physics course between them - don't.  (I ought to tell them I suppose.)

 wintertree 04 Mar 2021
In reply to Rob Exile Ward:

> Just a quick question - how many people posting here understand could explain the use of a $ sign in a formula?

am the dollar sign.

 wintertree 04 Mar 2021
In reply to A Nidderdale boulderer.:

I can’t understand from the formula what you’re trying to do.  If you post the quantities and what you want as a percentage of what, it’d help.

It’d help to know if they’re a quiet veggie or an in your face veggie.

 marsbar 04 Mar 2021
In reply to Rob Exile Ward:

Love the $.  Was lucky to have a good course on such things when I was briefly unemployed in the mid 90s. Funded by the EU as it happens.  

 kathrync 04 Mar 2021
In reply to wintertree:

> I am the dollar sign.

Is this the Excel equivalent of I am root?!

In reply to A Nidderdale boulderer.:

I would lay off the coke and ket; you might make more sense then...

As others have said, SUM is used inappropriately here.

However, if cell B1 is formatted as percentage, the rest of the formula will work to compute a percentage reduction in, say, price.

Formatting a cell as percentage allows display and entry in percent, so if you enter 10, it will display as 10%, but the numerical value used in calculation will be 0.1. You have to format the cell before entering the value, though; if you enter 10 in the cell, and then format as a percentage, it will show 1000%.

In reply to Rob Exile Ward:

I absolutely can explain the use of $ in cell references...

 Hamfunk 04 Mar 2021
In reply to Rob Exile Ward:

> Just a quick question - how many people posting here understand could explain the use of a $ sign in a formula?

> I'm not getting at anyone or trying to be a smartarse, I'm just intrigued really...

The $ locks the cell reference, so $A$1 locks the cell reference by row and column so if you drag your formula to other cells it will retain the reference to A1. Without the dollar sign it would move the reference in to maintain relative location of the reference.

The first $ ($A1) locks the column so if you drag a formula right across columns it wont move the A1 to B1, etc, but if you drag a formula down the rows it will move the reference (A1 to A2)

The second $ (A$1) locks the row so draging down wont will stay locked at A1 but dragging right will allow it to move the reference from A1 to B1

$A$1 locks both column and row.

Hope that makes sense......

(Handy tip: hitting F4 when selecting a cell in a formula adds the $ signs and locks the cell reference by column and row)

Post edited at 11:24
 DundeeDave 04 Mar 2021
In reply to A Nidderdale boulderer.:

Either

=A1*(1-B1)

Or

=A1*(1-(B1/100))

Depending on how you have recorded the percentage in B1

 Martin Hore 04 Mar 2021
In reply to Rob Exile Ward:

> Just a quick question - how many people posting here understand could explain the use of a $ sign in a formula?

> I'm not getting at anyone or trying to be a smartarse, I'm just intrigued really...

I certainly do know how to use the % sign. But, as an "amateur" user, it's easy to think you're quite proficient in Excel and still be missing something that others think is obvious. Every year, as a volunteer election agent, I need to process our local electoral register. It has 90,000 rows. It was only last year that a friend who works with Excel every day pointed out that double clicking the little "+" drags a formula down the whole sheet automatically. Prior to that I was manually dragging all the way down through 90,000 rows! Arghhhh!

Martin 

 wintertree 04 Mar 2021
In reply to kathrync:

> Is this the Excel equivalent of I am root?!

In my head, it sounded closer to “I am the one who knocks”...

 john arran 04 Mar 2021
In reply to Martin Hore:

> I certainly do know how to use the % sign. But, as an "amateur" user, it's easy to think you're quite proficient in Excel and still be missing something that others think is obvious. Every year, as a volunteer election agent, I need to process our local electoral register. It has 90,000 rows. It was only last year that a friend who works with Excel every day pointed out that double clicking the little "+" drags a formula down the whole sheet automatically. Prior to that I was manually dragging all the way down through 90,000 rows! Arghhhh!

> Martin 

... except you need to be very careful doing this. If there's a missing value in the adjacent column, it may only copy your formula down as far as the last value before the missing one, which can be a pretty hard bug to track down.

 Timmd 04 Mar 2021
In reply to Blue Straggler:

I think he is FH, aka Francis Holland, IIRC it's him who resides in the area of Nidderdale.

Sometimes referred to as 'Fat Hamster'...

Post edited at 13:13
In reply to Martin Hore:

> I certainly do know how to use the % sign.

What about the use of $, though...?

 ianstevens 04 Mar 2021
In reply to captain paranoia:

Excel lent

 ctranter 04 Mar 2021
In reply to Rob Exile Ward:

The explanation would be relatively absolute.

 Dr.S at work 04 Mar 2021
In reply to Hamfunk:

Well that’s helpful to know!

I use excel quite a lot but only at a fairly noddy level - never had it taught to me and I’m astounded that I know more than many of my PhD holding colleagues.

 Doug 04 Mar 2021
In reply to Dr.S at work:

Many years ago, along with several colleagues, I was sent on a one day Excel for intermeadiate users course. All of us were self taught & ended up teaching the instructor various functions she didn't know. Don't think any of us learnt much although none of us would have claimed to be 'advanced users', let alone experts.

 Cobra_Head 04 Mar 2021
In reply to Rob Exile Ward:

> .....  (I ought to tell them I suppose.)

Wow! I supose it comes down to how often you use it, and maybe how tenatious a person might be, I don't use it a lot but have done for 20+ years.

But I've always been one for find out "why" about things, so this leads you down a few paths you might not normally go down.

"IT" in schools is piss poor though, kids should be coming out with a basic knowledge of most computer programs used in the workplace, I'd class "$" as pretty basic knowledge.

 tallsteve 04 Mar 2021
In reply to A Nidderdale boulderer.:

The question is meaningless.  A % is always the % of something.  So without what your trying to find the percentage of there's no way to work out the correct formula, and as has been stated the sum makes even less sense.  Tsk.  1/10. See me in the morning .

It looks like you want the amount of money left when you remove some %.  i.e. how much is left if I spend 30% (B1) of £250 (A1); so thats £250 after I remove 30% of £250

Assuming A1 has already been formatted in currency, and B1 has already been formatted as a % field you could use either =A1-(A1*B1) or =A1*(1-B1)

or you could be wanting just 30% of £250 which is =A1*B1

 tallsteve 04 Mar 2021
In reply to Route Adjuster:

It probably was taught at school.  The things my kids were taught in their IT lessons was shocking! suspect the teachers teaching IT didn't know how it all worked either.  Hopefully the next generation of kids that grew up with this stuff will be better.

 AndyC 04 Mar 2021
In reply to captain paranoia:

> I absolutely can explain the use of $ in cell references...

Bonus points if you can explain the implicit intersection operator that has appeared in Excel 365... imagine my surprise when "@" symbols started peppering my formulae!

 john arran 04 Mar 2021
In reply to AndyC:

> Bonus points if you can explain the implicit intersection operator that has appeared in Excel 365... imagine my surprise when "@" symbols started peppering my formulae!

Excel's array formulae always seemed to me to be a disturbing form of dark magic. You could write one but then you couldn't easily see what you'd written and you couldn't easily edit it. At least now they've made them visible, so it no longer feels like we're creating formulae out of plasma..

 MG 05 Mar 2021
In reply to Martin Hore:

Ctrl+shft+arrow will be useful to you.

 MG 05 Mar 2021
In reply to Cobra_Head:

I dont really see how using excel without $ is possible! However, I  only learned about sumproduct the other day...

 wintertree 05 Mar 2021
In reply to MG:

> Ctrl+shft+arrow will be useful to you.

You can't just go giving stuff like that away.

Watching undergraduates use a computer without knowing any keyboard shortcuts is painful. Even simple ones like "up arrow" on a console to go through past commands...

 Rob Exile Ward 05 Mar 2021
In reply to MG:

So long as you can use the if, vlookup and vlookup functions - oh and the string ones - and of course the date ones, you'll be fine.

 Michael Hood 05 Mar 2021
In reply to Rob Exile Ward & ...:

I use Excel a fair bit, home & work - in fact I joke that I'm basically paid to mess about with data in Excel, and my family joke "he's put it on a spreadsheet". I would however never claim to be an expert because one of the main things I've found about Excel is that there is always so much I still don't know.

Often it's simple things that can be done in several ways and I've just habitually used one so I've never become aware of the alternatives. Other times it's "that's really cool (geek alert), I didn't know you could do that"

The main attitudes that will help with getting more out of Excel are IMO:

  • Don't be afraid to explore/blunder about trying to do something you've not done before.
  • Realise that if you want to do something in Excel, then the chances are that it's already been done by somebody and can be found on the internet.
 wintertree 05 Mar 2021
In reply to AndyC:

> Bonus points if you can explain the implicit intersection operator that has appeared in Excel 365... imagine my surprise when "@" symbols started peppering my formulae!

That bit me on the rear a few months ago.  What a complicated mess there is behind the scenes.

 ThunderCat 05 Mar 2021
In reply to Rob Exile Ward:

> Just a quick question - how many people posting here understand could explain the use of a $ sign in a formula?

> I'm not getting at anyone or trying to be a smartarse, I'm just intrigued really...

 I 'Absolutely' agree...

 Rob Exile Ward 05 Mar 2021
In reply to ThunderCat:

Well that's 'relatively' encouraging...

 ThunderCat 05 Mar 2021
In reply to wintertree:

> You can't just go giving stuff like that away.

> Watching undergraduates use a computer without knowing any keyboard shortcuts is painful. Even simple ones like "up arrow" on a console to go through past commands...

There was a spell in the late 90's where my mouse stopped working in the office and I was too lazy / poor to get a new one for weeks...I was forced to learn a load of keyboard shortcuts for everything and they've gradually become engrained in muscle memory.  I do them without thinking now and it pains me when I see clicking on menu options

Yeah, Excel nerd and proud of it. 

 Martin Hore 05 Mar 2021
In reply to john arran:

> ... except you need to be very careful doing this. If there's a missing value in the adjacent column, it may only copy your formula down as far as the last value before the missing one, which can be a pretty hard bug to track down.

Good point John. I do always check that it's reached the bottom - much quicker than dragging down manually.

Martin

 Martin Hore 05 Mar 2021
In reply to captain paranoia:

> > I certainly do know how to use the % sign.

> What about the use of $, though...?

I meant $ of course. % was a typo. And, yes, I do know that that sort of typo can create havoc in Excel....

Thank you though. I think I'm pretty clued up on the $ sign.

Martin

 lithos 06 Mar 2021
In reply to nufkin:

it is simple but you need to remember that the Given Total  (GT) represents 120%  (assuming VAT 20%)

so you need  VAT = (GT / 120) * 20

A           B          C                   D       E

GT        VAT       PreVAT         20     .16666

120       20         100

if you put VAT rate into cell  D1, ie 20  then into  E1  the formulae = $D$1 / (100 + $D$1)

then B2 = A2 * $E$1  and C2 = A2 - B2  and fill em down (cols B and C) and type your totals you want to partition into col A.  You can tweak the table by changing D1 for different VAT rates

Post edited at 16:50
Removed User 06 Mar 2021
In reply to A Nidderdale boulderer.:

UKC.

Where the formula for percentage in Excel gets 50 posts.

 MG 06 Mar 2021
In reply to wintertree:

I remember watching someone who really knew how to use emacs and realizing mice and windows are vastly overrated.

 Luke90 06 Mar 2021
In reply to Rob Exile Ward:

> So long as you can use the if, vlookup and vlookup functions - oh and the string ones - and of course the date ones, you'll be fine.

Nah, if you're starting from scratch, learn INDEX and MATCH instead of VLOOKUP. Way more flexible, and apparently give better performance too (though to an extent that would only matter on huge spreadsheets).

 Luke90 06 Mar 2021
In reply to tallsteve:

> It probably was taught at school.  The things my kids were taught in their IT lessons was shocking! suspect the teachers teaching IT didn't know how it all worked either.  Hopefully the next generation of kids that grew up with this stuff will be better.

The whole "digital natives" thing is really oversold. Modern kids aren't naturally becoming ace computer users any more than kids growing up before computers automatically became great at using typewriters and filing cabinets. They're becoming good at the technological things they actually care about, which for most of them is using social media apps on a touchscreen. They can all use a keyboard and mouse but they're not, on the whole, noticeably excellent at it. Excel certainly isn't interesting to many of them. The first generations of kids that grew up with ubiquitous easy access to computers has been in the workplace for a good while already, and they're not setting it ablaze.

Don't get me wrong, the same kids that would have been psyched to tinker with Basic scripts on BBC computers back in the day are now building incredible things with Python, but they're not vastly more common in this generation than that one.

(Don't judge the teachers too harshly, their hands are often quite tied when it comes to choice of software and syllabus.)

 wintertree 06 Mar 2021
In reply to MG:

> I remember watching someone who really knew how to use emacs and realizing mice and windows are vastly overrated.

For my sins, about 15 years ago I spent 4 months writing a lot of VHDL.  By the end of that I was in to Emacs in a big way.  

I have been writing up my children's short stories - if I successfully escape from academia I'm going to need to find something to replace the endless negativity of the various review processes and trying to get a story collection published fits the bill I think.  For this I use a text mode word processor called WordGrinder running an old MacBook Air that's just got a console only Linux install on it.  No alt-tabbing to UKC with that...

 wercat 07 Mar 2021
In reply to Luke90:

my son started on an Acorn Electron and progressed to a BBC B in 2010.  He's now modeling RNA molecules at Leicester in 3D in a tool he wrote in BBC BASIC for Windows.  The language still lives on, running at speeds that couldn't have been dreamt of even on an Archimedes thanks to the original author who ported it on to Z80 for Acorn and has continued to keep the language working on modern processors and now even Linux

Post edited at 12:32
 nufkin 07 Mar 2021
In reply to lithos:

>  it is simple but you need to remember that the Given Total  (GT) represents 120%

Thanks!

That was caused me the most trouble. Your method is much more elegant than whatever I eventually ended up bodging together, so next time I need to use it I'll replace it with your formula

In reply to wintertree:

> For this I use a text mode word processor called WordGrinder running an old MacBook Air that's just got a console only Linux install on it.

I have an AWK script I wrote to format little 'aide memoire' booklets for DofE use; essentially a special-to-type word processor. It takes a text file with a custom format tagging, and spits out PostScript...


New Topic
This topic has been archived, and won't accept reply postings.
Loading Notifications...