Excel quick question

  Allan-263226 19:56 24 Mar 04
Locked

I know its dead easy but not working my end...

Anyhow question is this:

Costprice+Costprice*Profit


Example is: £3.50+£3.50*40% (£3.50=Costprice/40%=40% VAT)

So I tried something like this in Excel
=SUM(A12+A12*40) which is giving £4.90

I need it to give me £9.80

Course it didnt work the above only calculated for one A12...one Costprice only, tried few other things but no show

  VoG II 19:58 24 Mar 04

=A12 + 1.4*A12

  Allan-263226 20:01 24 Mar 04

Cheers for the quick response

That only gives me £8.40 not £9.80

  Greenfingers 20:04 24 Mar 04

Place brackets (3.50+3.50)*40

  Allan-263226 20:07 24 Mar 04

=SUM(A12+A12)*140%

That seems to have done the trick, dunno how though!

  Greenfingers 20:20 24 Mar 04

=SUM(((A12+A12)*40%+((A12+A12))))=£9.80

  joethebow 20:21 24 Mar 04

It's simple maths. The multiplication is always performed before the addition unless the addition is put in parenthesis.

so
4 + 6 X 2 = 16 i.e. 6x2=12+4=16

whereas
(4 + 6) X 2 = 20 i.e. 4+6=10x2=20.

The rules of maths say that calculations are performed in this order:

1. Multiply.
2. Divide.
3. Add.
4. Subtract

Unless the lower order calculation is enclosed in parenthesis.

  interzone55 21:20 24 Mar 04

I've always used this Mnemonic

BoDMAS

Brackets - Division - Multiplication - Addition - Subtraction

The division & multiplication are carried out in the order they appear, same with addition & subtraction

  daba 21:50 24 Mar 04

The usage of =SUM() in this case is not strictly correct.

SUM() is a function in its own right, and is not required in most Excel calculations. Many people incorrectly use =SUM(their expression), trying to tell Excel to calculate the maths (the "sum"), whereas SUM is short for Summate, meaning to 'Add-Up'

You generally need to use the function =SUM() to 'summate' a 'range' of cells, for example, =SUM(A12:A22). That would add up all the values in cells A12 to A22 inclusive. Notice the ':' between the cell references to distinguish them as a 'range' specification.

To achieve your answer, you could write a simple 'expression' without the SUM function, (as in VoG's post), but by including the parenthesis as in Greenfingers' post :-

=(Costprice1+Costprice2)*1.4

where Costprice1 and 2 are both £3.50, giving £7.00 * 1.4 = £9.80.

The '1.4' is 1 for the sum of the costprices, plus .4 (40%) for the markup.

If you had a longer list of items to work with, other than your 2, then =SUM() comes into play proper.

For instance, if as above, Cells A12 to A22 contained a list of costprices 1 to 10, then the formula =SUM(A12:A22)*1.4 is certainly clearer than the equivalent longwinded expression:-

=(A12+A13+A14+A15+A16+A17+A18+A19+A20+A21+A22)*1.4

Hope this is clear, and it helps to make your expressions tidier and more correct syntactically.

  Cesar 10:39 25 Mar 04

The financial functions in Excel are wrong the Interest function is based on the American way e.g Interest/12 the British way is more complicated e.g ((Interest/100+1)^12-1)*100 of course the /100 and multiply by 100 are not neccessary if the cell is formatted to %


joethebow
The rules of maths: Exponent comes before Multiplication.

This thread is now locked and can not be replied to.

What is Amazon Go and will it come to the UK? The store without checkouts or queues

1995-2015: How technology has changed the world in 20 years

Why ecommerce hasn't taken off on social media

New MacBook Pro 2016 review | MacBook Pro with Touch Bar review: Apple's expensive and powerful…