Page 1 of 2 12 LastLast
  1.    28 Sep 2017 #1
    Join Date : May 2016
    Cebu City
    Posts : 22
    Microsoft Windows 10 Pro 64-bit 14393.693

    MS EXCEL (Math: Product / Addition)


    Click image for larger version. 

Name:	Capture.PNG 
Views:	33 
Size:	9.9 KB 
ID:	155354
    how to make auto calculate

    example, C * B = E (each item) and E = F

    should look like this
    Click image for larger version. 

Name:	Capture .PNG 
Views:	33 
Size:	9.2 KB 
ID:	155355

    please help, thanks!
      My ComputerSystem Spec
  2.    28 Sep 2017 #2
    Join Date : Dec 2013
    Portsmouth Hampshire
    Posts : 1,829
    Windows 10 x86 14383 Insider Pro and Core 10240

    Created an empty row below the column headings and hid it (for calculation of running total in column F).

    Formatted the data in column C as currency (in my case as £)

    Filled in columns A, B, C.

    in the SUBTOTAL column E4 against the first ITEM entered the formula =B4*C4 and <enter>

    in the RUNNING TOTAL column F4 against the first item entered the formula =E4+F3 and <enter>

    select cells E4 and F4, and extend the selection down as required, and press Ctrl+D (copy down)

    Click image for larger version. 

Name:	image.png 
Views:	7 
Size:	65.1 KB 
ID:	155357

    Microsoft OneDrive - Access files anywhere. Create docs with free Office Online.
      My ComputersSystem Spec
  3.    28 Sep 2017 #3
    Join Date : Feb 2016
    Maribor, Slovenia
    Posts : 8,918
    Windows 10 (Pro and Insider Pro)

    Quote Originally Posted by DW7EZV View Post
    Click image for larger version. 

Name:	Capture.PNG 
Views:	33 
Size:	9.9 KB 
ID:	155354
    how to make auto calculate

    example, C * B = E (each item) and E = F

    should look like this
    Click image for larger version. 

Name:	Capture .PNG 
Views:	33 
Size:	9.2 KB 
ID:	155355

    please help, thanks!
    Start in command row with = and then click on first cell to multiply, press * and click on second cell.


    After this look at Fafhrd's post (beats me by seconds ...)
    Attached Thumbnails Attached Thumbnails 2017_09_28_09_42_541.png  
      My ComputerSystem Spec
  4.    28 Sep 2017 #4
    Join Date : Dec 2015
    Posts : 5,920
    Windows10

    Quote Originally Posted by Fafhrd View Post
    Created an empty row below the column headings and hid it (for calculation of running total in column F).

    Formatted the data in column C as currency (in my case as £)

    Filled in columns A, B, C.

    in the SUBTOTAL column E4 against the first ITEM entered the formula =B4*C4 and <enter>

    in the RUNNING TOTAL column F4 against the first item entered the formula =E4+F3 and <enter>

    select cells E4 and F4, and extend the selection down as required, and press Ctrl+D (copy down)

    Click image for larger version. 

Name:	image.png 
Views:	7 
Size:	65.1 KB 
ID:	155357

    Microsoft OneDrive - Access files anywhere. Create docs with free Office Online.
    You do not need a hidden row - it is overcomplicating things!

    Simply set cell F4 as "=E4"

    Then cell F5 simply uses "=E5 + F4"

    Then copy formula from cell F5 to other cells in F column.
      My ComputerSystem Spec
  5.    28 Sep 2017 #5
    Join Date : Dec 2013
    Portsmouth Hampshire
    Posts : 1,829
    Windows 10 x86 14383 Insider Pro and Core 10240

    Yes! there are many ways to skin a cat @cereberus!

    However my method allows the copy down command to be run on the first row of data, rather than your method, on the second row of data, with different formulae in the first row.
      My ComputersSystem Spec
  6.    28 Sep 2017 #6
    Join Date : Dec 2015
    Posts : 5,920
    Windows10

    Quote Originally Posted by Fafhrd View Post
    Yes! there are many ways to skin a cat @cereberus!

    However my method allows the copy down command to be run on the first row of data, rather than your method, on the second row of data, with different formulae in the first row.
    Sure but (IMO) if you are going to do it your way, it is clearer if you do not hide the row, and simply put 0 in cell F3, and this also allows you to have e.g. an initial one off setup cost included in cell F3.
      My ComputerSystem Spec
  7.    28 Sep 2017 #7
    Join Date : Dec 2013
    Portsmouth Hampshire
    Posts : 1,829
    Windows 10 x86 14383 Insider Pro and Core 10240

    Quote Originally Posted by cereberus View Post
    Sure but (IMO) if you are going to do it your way, it is clearer if you do not hide the row, and simply put 0 in cell F3, and this also allows you to have e.g. an initial one off setup cost included in cell F3.
    Now that is getting complicated!

    As I said cat skinning can get complicated, and messy, so IMO it's better just to hide it, and if you can avoid inventing numericals where null values (empty cells) can be used instead, it's less work, and less difficult to debug a spreadsheet where formula are consistent in rows and columns where real raw data exist alongside calculated values.
      My ComputersSystem Spec
  8.    28 Sep 2017 #8
    Join Date : Dec 2015
    Posts : 5,920
    Windows10

    Quote Originally Posted by Fafhrd View Post
    Now that is getting complicated!

    As I said cat skinning can get complicated, and messy, so IMO it's better just to hide it, and if you can avoid inventing numericals where null values (empty cells) can be used instead, it's less work, and less difficult to debug a spreadsheet where formula are consistent in rows and columns where real raw data exist alongside calculated values.
    How can leaving a row in empty be more complicated? You do not even need to enter zero, or you can hide display of zero etc.

    The extra about offset costs was just a possible advantage of doing it my way, and not really pertinent to OP's problem.

    The main plus of hiding row here is that it does prevent a user entering an inappropriate figure in that cell, but one should really use cell locking for fixed data and formulae if making a robust spreadsheet..

    To me hiding rows is for when doing lots of intermediate calculations that would make spreadsheet cumbersome.
    I actually tend to use other sheets for intermediate calculations and hide sheet as quicker and eaiser for complicated spreadsheets but that is just my preference.


    Anyway, we will just have to disagree on style - it is up to OP to decide which he prefers.

    .
      My ComputerSystem Spec
  9.    28 Sep 2017 #9
    Join Date : May 2016
    Cebu City
    Posts : 22
    Microsoft Windows 10 Pro 64-bit 14393.693
    Thread Starter

    Quote Originally Posted by Fafhrd View Post
    Created an empty row below the column headings and hid it (for calculation of running total in column F).

    Formatted the data in column C as currency (in my case as £)

    Filled in columns A, B, C.

    in the SUBTOTAL column E4 against the first ITEM entered the formula =B4*C4 and <enter>

    in the RUNNING TOTAL column F4 against the first item entered the formula =E4+F3 and <enter>

    select cells E4 and F4, and extend the selection down as required, and press Ctrl+D (copy down)

    Click image for larger version. 

Name:	image.png 
Views:	7 
Size:	65.1 KB 
ID:	155357

    Microsoft OneDrive - Access files anywhere. Create docs with free Office Online.
    why i input in QTY and PRICE, only the subtotal will auto calculate. not the running total. running total will only work if i ctrl + d. is ther a way to auto calculate? thanks and sorry my english is bad.
    Click image for larger version. 

Name:	Capture.PNG 
Views:	20 
Size:	13.4 KB 
ID:	155375
      My ComputerSystem Spec
  10.    28 Sep 2017 #10
    Join Date : Dec 2013
    Portsmouth Hampshire
    Posts : 1,829
    Windows 10 x86 14383 Insider Pro and Core 10240

    If you select cells E4 and F4, and then continue the selection down , for say 100 rows, with <Shift>+<down arrow> keys, then enter <Ctrl>+D to copy down, then the formulae in both E4 and F4 should be copied down in those 100 rows. You only seem to have copied down in the column E. You can check whether there are formulae in a cell by clicking it and checking what is in the cell edit box.
      My ComputersSystem Spec

 
Page 1 of 2 12 LastLast


Similar Threads
Thread Forum
Solved Excel ?
I'm trying to learn how to setup a spreadsheet in Excel and need a little help. This is my first attempt at making a spreadsheet. In this example how do I setup this spreadsheet so that I can keep a running total of columns C,D and E in column F if...
Software and Apps
Internal Or External HD Addition ?
Hello, New desktop pc that came with 1 TB HD. Debating installing another internal 1 TB HD vs just using an external one. Lots of Photos, and another user with lots of video, etc. The only advantage that I can see for another internal one...
General Support
Can we force-install a HID Audio device IN ADDITION to OEM?
Conexant Audio devices have been troublesome for Windows 10 since the earliest previews, and still are. on 2 YOga 2's I config'd, on one HP AMD-based G4, on one Thinkpad I've worked with - all Conexant drivers intalled would fail sooner or later ...
Drivers and Hardware
Solved Win 10 Alt-Tab Order - Dword Addition - Will it work?
For keyboard nerds like me http://www.bleepingcomputer.com/forums/public/style_emoticons/default/laugh.png, this is one that I use many times daily. When I installed Win 7 back in 2010, I added this dword to revert the alt-tab order to the way it...
General Support
Our Sites
Site Links
About Us
Windows 10 Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 10" and related materials are trademarks of Microsoft Corp.

© Designer Media Ltd
All times are GMT -5. The time now is 21:59.
Find Us
Twitter Facebook Google+ Ten Forums iOS App Ten Forums Android App



Windows 10 Forums