Excel quick question

  Allan-263226 19:56 24 Mar 2004
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 2004

=A12 + 1.4*A12

  Allan-263226 20:01 24 Mar 2004

Cheers for the quick response

That only gives me £8.40 not £9.80

  Greenfingers 20:04 24 Mar 2004

Place brackets (3.50+3.50)*40

  Allan-263226 20:07 24 Mar 2004

=SUM(A12+A12)*140%

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

  Greenfingers 20:20 24 Mar 2004

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

  joethebow 20:21 24 Mar 2004

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 2004

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 2004

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 2004

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.

Elsewhere on IDG sites

OnePlus 5T review

How to draw a mandala

iPhone X review

Les meilleures enceintes Bluetooth à moins de 150 euros