Page 2 of 2 FirstFirst 12
Results 11 to 17 of 17

Thread: Excel Help please

  1. #11
    Diamond Member
    Join Date
    Mar 2010
    Location
    Cape Town
    Posts
    6,329
    Thanks
    426
    Thanked 978 Times in 795 Posts
    The best way of looking at this sort to problem is to figure out how to do it in intermediate steps. Nothing stops you from creating intermediate "work" tables to put the data into a more useable form. You could create an intermediate table that is used simply to group together the data into a more useable form. You could use vlookup to populate the table based on the criteria in column C. The trick with using Excel is to realize that you do not have to create the perfect in-cell formula in one shot. I think that when an in-cell formula becomes too complex and you are unable to evaluate the intermediate results then you are looking for trouble.

    You can do it in various ways.

    option 1
    Drop the subtotals.
    Grab the data and sort it by Column C
    Auto subtotals for every change in column C.

    option 2
    Drop the subtotals.
    Create a new set of rows below your data and call the A1 to A8
    Now create a formula in each field using =sumif(C1..C10,"A1",{whatever column you are trying to sum}
    sumif is very clever because it looks a criteria to decide whether to sum the value sumif (criteria range, criteria, sum range)

    option 3
    You could write a VB script to run through the data but i think it would be an overkill.

    Try these methods, if you don't come right let me know thn I'll do it for you.

  2. #12
    New Member
    Join Date
    Sep 2012
    Location
    Cape Town
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the feedback. Was hoping to do it in one magical formuale but have effectively just created 4 totals for each "T" and then used a If and nested Sumif. Seems to have worked.

    Thanks again

    G

  3. #13
    Gold Member Houses4Rent's Avatar
    Join Date
    Mar 2014
    Location
    Cape Town
    Posts
    803
    Thanks
    8
    Thanked 56 Times in 52 Posts

    Excel help needed

    I want a simple graph over my business stats over several years.

    All fine, but now I want to add another set of 12m data.
    I right click on graph and SELECT DATA. That field is small and my formula is long so I just copy it, update it elsewhere, but it does not allow me to paste the updated new formula back in. Just makes a sound like when one does something not possible.

    Is there maybe a limit how long a formula can be?

    This works (current)
    ='Marc'!$E$1:$P$1,'Marc'!$R$1:$AC$1,'Marc'!$AE$1:$ AP$1,'Marc'!$AS$1:$BD$1,'Marc'!$BG$1:$BR$1,'Marc'! $BU$1:$CF$1,'Marc'!$CI$1:$CT$1,'Marc'!$E$4:$P$4,'M arc'!$R$4:$AC$4,'Marc'!$AE$4:$AP$4,'Marc'!$AS$4:$B D$4,'Marc'!$BG$4:$BR$4,'Marc'!$BU$4:$CF$4,'Marc'!$ CI$4:$CT$4

    but this does not work (updated):
    =Marc!$E$1:$P$1,Marc!$R$1:$AC$1,Marc!$AE$1:$AP$1,M arc!$AS$1:$BD$1,Marc!$BG$1:$BR$1,Marc!$BU$1:$CF$1, Marc!$CI$1:$CT$1,Marc!$CW$1:$DH$1,Marc!$E$4:$P$4,M arc!$R$4:$AC$4,Marc!$AE$4:$AP$4,Marc!$AS$4:$BD$4,M arc!$BG$4:$BR$4,Marc!$BU$4:$CF$4,Marc!$CI$4:$CT$4, Marc!$CW$4:$DH$4

    Can anybody help?
    Houses4Rent
    "We treat your investment as we treat our own"
    marc@houses4rent.co.za www.houses4rent.co.za
    083-3115551
    Global Residential Property Investor / Specialized Letting Agent & Property Manager

  4. #14
    Diamond Member
    Join Date
    Mar 2010
    Location
    Cape Town
    Posts
    6,329
    Thanks
    426
    Thanked 978 Times in 795 Posts
    I spy with my little eye....lots of missing quotes around each and every.... Marc in the second expression. Maybe that is the problem?

  5. #15
    Gold Member Houses4Rent's Avatar
    Join Date
    Mar 2014
    Location
    Cape Town
    Posts
    803
    Thanks
    8
    Thanked 56 Times in 52 Posts
    The copy and paste seems to have dropped a lot of things.
    I figured it out in third attempt, I had given up twice before until I asked here.
    I right click SELECT DATA and then hit ENTER. That makes the formula appear on the top section of sheet and its much easier to update it there.
    Houses4Rent
    "We treat your investment as we treat our own"
    marc@houses4rent.co.za www.houses4rent.co.za
    083-3115551
    Global Residential Property Investor / Specialized Letting Agent & Property Manager

  6. #16
    Diamond Member
    Join Date
    Mar 2010
    Location
    Cape Town
    Posts
    6,329
    Thanks
    426
    Thanked 978 Times in 795 Posts
    hmmm...Excel formula editing is a pain butt.

  7. #17
    Gold Member Houses4Rent's Avatar
    Join Date
    Mar 2014
    Location
    Cape Town
    Posts
    803
    Thanks
    8
    Thanked 56 Times in 52 Posts
    The way I do it now works quite well.
    Houses4Rent
    "We treat your investment as we treat our own"
    marc@houses4rent.co.za www.houses4rent.co.za
    083-3115551
    Global Residential Property Investor / Specialized Letting Agent & Property Manager

Page 2 of 2 FirstFirst 12

Similar Threads

  1. Pastel Partner to Excel Auto mapping
    By tyrone k in forum Technology Forum
    Replies: 2
    Last Post: 11-May-09, 04:59 PM
  2. [Article] Nifty Excel spread sheets
    By IanF in forum General Business Forum
    Replies: 7
    Last Post: 05-Dec-08, 12:14 PM
  3. SASOL & EXCEL in trouble
    By Sieg in forum General Business Forum
    Replies: 5
    Last Post: 21-Oct-08, 11:17 AM

Tags for this Thread

Did you like this article? Share it with your favourite social network.

Did you like this article? Share it with your favourite social network.

Posting Permissions

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