+ Reply to Thread
Results 1 to 7 of 7

Formula performed across all columns to return value in a single cell

  1. #1
    Registered User
    Join Date
    06-24-2016
    Location
    London, England
    MS-Off Ver
    2007
    Posts
    26

    Formula performed across all columns to return value in a single cell

    Hi all,

    My apologies if this has been posted elsewhere - I'm a relative novice and unsure of the terminology which could describe my request.

    I have a data sheet stretching from A1:AK225. In column AL, say, I would like to include a cell with a formula that will do the following:

    = (B5/($B$3*$B$4)) + (C5/($C$3*$C$4)) + (D5/($D$3*$D$4)).... and so on up to column AK. Once I've done this once, I'd fill in down to row 225.

    Is there a formula I can use which will allow me to do all of this without entering manually (which I have already done in my desperation!)

    Many thanks,

    SW

  2. #2
    Forum Expert millz's Avatar
    Join Date
    08-14-2013
    Location
    Singapore
    MS-Off Ver
    Excel, Access 2016
    Posts
    1,694

    Re: Formula performed across all columns to return value in a single cell

    I believe there's a limit to formula characters length too. I suggest using VBA to calculate and populate the result in column AK.
    多么想要告诉你 我好喜欢你

  3. #3
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Formula performed across all columns to return value in a single cell

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)

  4. #4
    Registered User
    Join Date
    06-24-2016
    Location
    London, England
    MS-Off Ver
    2007
    Posts
    26

    Re: Formula performed across all columns to return value in a single cell

    Hi Ankur,

    I've attached an example worksheet.

    The value in cell G4 should be 165,408.8 - from (B4/(B2*B3)+(C4/(C2*C3)+(D4/(D2*D3)+(E4/(E2*E3)+(F4/(F2*F3)

    I was hoping there was a way to do this without entering each cell reference manually, as I have done here (I have data up to column AK at the moment, and this is likely to increase).

    Thanks

    SW
    Attached Files Attached Files

  5. #5
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Formula performed across all columns to return value in a single cell

    Try

    G4=SUMPRODUCT((B4:F4)/(B2:F2*B3:F3))

  6. #6
    Registered User
    Join Date
    06-24-2016
    Location
    London, England
    MS-Off Ver
    2007
    Posts
    26

    Re: Formula performed across all columns to return value in a single cell

    Thank you Ankur - you've solved a problem I was struggling with (and taught me a new tool at the same time!)
    I think I need to invest in some kind of excel dictionary, I never know quite how to ask the questions I need the answer to!

  7. #7
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,935

    Re: Formula performed across all columns to return value in a single cell

    hahahah You should Please mark thread as solved and add reputation by clicking star icon * left corner on your scree on those post which helps you..

    Glad to help you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 08-16-2016, 07:58 AM
  2. [SOLVED] Return value 2 columns to left of cell in single row
    By AndyGW in forum Excel General
    Replies: 6
    Last Post: 06-05-2016, 07:03 PM
  3. [SOLVED] vLookup multiple values separated by ";" in single cell / return result in single cell
    By Tunesmith in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-07-2015, 12:02 PM
  4. Replies: 11
    Last Post: 06-27-2014, 08:11 AM
  5. Using multiple columns in single cell formula
    By madadd in forum Excel General
    Replies: 3
    Last Post: 02-23-2011, 04:44 PM
  6. Filtering Across Columns and return in a single column
    By keith_chia in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-09-2008, 07:13 AM
  7. Replies: 2
    Last Post: 08-31-2006, 03:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1