+ Reply to Thread
Results 1 to 4 of 4

Everything to the left...

  1. #1
    Registered User
    Join Date
    07-30-2020
    Location
    London
    MS-Off Ver
    365
    Posts
    46

    Everything to the left...

    Probably a really easy one... definitely easy with a macro (CTRL+SHIFT+LEFT)


    I have a range of formulae in a column, currently like this:

    =MIN(A1:A3)
    =MAX(B1:B3)
    =AVERAGE(C1:C3)
    =SUM(D1:D3)
    =SUM(E1:E3)/15*100

    I would like to be able to copy all these formulae to another part of the sheet to essentially apply the formulae to 'everything left of the posted cells'

    i.e. if I posted them in K10

    =MIN(everything from K1-K9)
    =MAX(everything from L1-L9)
    =AVERAGE(everything from M1-M9)
    =SUM(everything from N1-N9)
    =SUM(everything from O1-O9)/(number of cells highlighted with values*5)*100

    I could cut and paste most of these formulae and/or write a macro, but I'd like to copy this to any workbook and not all are macro-enabled... I'm stuck with the last one in particular

    When I paste that formula to any workbook, I want the formula to say 'select all cells left of this cell, count how many cells have numerical values and multiply it by 5 in this case...

    =SUM(everything to the left of the pasted cell)/(COUNTA(number of cells to the left of the pasted cell with values)*5)*100 ???


    btw, I've posted a number of times on this forum and everyone is absolutely amazing... I've appreciated every second you've all put in to helping in the past!

  2. #2
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Everything to the left...

    A worksheet has two display modes... A1 (the column header displays letters) and R1C1 (the column header displays numbers). In A1 mode, you would need to set up one formula the way you want and then copy paste it to other locations. So, if you select, say, cell E5 and put this formula in it... =SUM($A5:D5) (note the A column reference is locked) then copy pasting it elsewhere will do what you want. While you can do the same thing in R1C1 mode, you can actually specify what you want directly without having to specifically know the column you are in in order to specify the column immediately to its left. In R1C1 mode, the above SUM formula would be written this way... =SUM(RC1:RC[-1]). The R with out anything attached to it means the "current row" no matter what that row is. The C1 means the first column absolutely (that is, the 1 says "first" column) and the C[-1] means the "current column" no matter what that column is offset by the number inside the square brackets, so the [-1] means the column one to the left of the current column. The thing to note is that for A1 mode, if I copied the formula =SUM($A5:D5) to, say, cell N9, the copied formula would become =SUM($A9:M9). On the other hand, the formula =SUM(RC1:RC[-1]) will be identically the same no matter what cell you copy it to. Think about that... you can write exactly what you asked about directly without having to pay attention to the cell you put the formula in (except if you put in a negative offset that takes you off the left side of the worksheet). Okay you are asking, how do I set up R1C1 mode? Simple. Open Excel Options, select the Formula item on the left and in the second group (titled "Working with formulas", the first item is a checkbox with the caption "R1C1 reference style"... check it to work in R1C1 mode and uncheck it to work in A1 mode. If you plan to toggle R1C1 on and off a lot, you can add "Options Formulas" (from "All Commands") to the QAT by clicking the QAT drop down arrow and picking "More commands". Then you can click its icon in the QAT area and toggle the setting quite easily.
    Last edited by Rick Rothstein; 07-31-2021 at 06:13 PM.

  3. #3
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,957

    Re: Everything to the left...

    If you want to sum cells A1 to C1, then put this formula in cell D1: =SUM(OFFSET(C1,0,-CELL("col",C1)+1,1,CELL("col",C1))).

    You may copy this relative formula to any cell and you may also change SUM to MIN, MAX or AVERAGE.

  4. #4
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    2,957

    Re: Everything to the left...

    As for "number of cells highlighted with values", what do you mean?

+ 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. [SOLVED] snake single column into multiple columns right to left and left to right
    By Gesssssssss in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-24-2021, 10:10 AM
  2. Replies: 2
    Last Post: 05-15-2021, 10:00 AM
  3. Formula to move data to Left when LEFT cell is blank?
    By wer5150 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-31-2019, 05:26 AM
  4. Formula to move data to Left when LEFT cell is blank?
    By wer5150 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-31-2019, 05:22 AM
  5. Replies: 6
    Last Post: 06-26-2017, 09:04 PM
  6. Replies: 1
    Last Post: 07-04-2005, 09:05 AM

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