Any excel guru's Help needed.

Discussion in 'Bad Dog Cafe' started by koolaide, Nov 19, 2017.

  1. koolaide

    koolaide Tele-Holic

    Posts:
    542
    Joined:
    Oct 11, 2009
    Location:
    south carolina
    Hi all,
    I am trying to build a worksheet to calcuate roll goods (carpet waste) based on room size. I an having trouble with a nested if formula. It returns an incorrect result. I am hoping if I post the formula here someone can spot the error.
    Note K1 is the cell where the width can be changed as the goods come in variable widths.
    =IF(D2<=K1,K1-D2,IF(D2>K1,K1*2-D2,IF(D2>K1*2,K1*3-D2)))

    let me know if you need more information.

    Thanks all for looking,
    Jim
     
  2. Tommy Biggs

    Tommy Biggs Friend of Leo's

    Posts:
    2,220
    Joined:
    Jun 17, 2010
    Location:
    Northern NJ
    Try parentheses around your calculations inside your nested ifs?
    (K1-D2)...((K1*2)-D2)
     
    koolaide likes this.
  3. tjnies

    tjnies Tele-Holic

    Posts:
    560
    Joined:
    Dec 14, 2009
    Location:
    Latrobe PA
    I'm fairly good at Excel, but admittedly didn't look deeply into this question (yet).

    On the surface, be careful when you use K1*2-D2 formulas. Use more parentheses to isolate each section of the calculation. In this way you can make sure each part calculate properly.

    I also have a concern about D2<=K1. Why not make it more efficient by changing to K1>D2?

    Another way to do this is to make sure each IF statment on its own works properly, then nest it within the next level, and so on.
     
    moosie, Tommy Biggs and koolaide like this.
  4. ddewerd

    ddewerd Friend of Leo's Ad Free Member

    Age:
    59
    Posts:
    2,419
    Joined:
    Nov 14, 2003
    Location:
    Willow Springs, Great-State-of-Texas
    looks to me like your nested statement is always true for the first argument, therefore you will never get the second one

    if D2>K1, it doesn't matter if it's also >k1*2 because you'll never get to process it

    what if you swap the order of the middle set of arguments, and put the d2>k1*2 first, then you don't need the final IF statement

    =IF(D2<=K1,K1-D2,IF(D2>K1*2,k1*3-d2,K1*2-D2)

    too early this morning, not enough coffee yet, and trying to do this on my tablet, so hopefully this makes sense

    Cheers,
    Doug
     
    SparkleFart, koolaide and AAT65 like this.
  5. jvin248

    jvin248 Poster Extraordinaire

    Posts:
    8,394
    Joined:
    Apr 18, 2014
    Location:
    Lions & Tigers oh Mi !
    .

    When I run into a long formula problem, I start splitting out each set of calculations into test cells.
    So with yours I might go these cells on the side of your sheet
    =IF(D2<=K1,K1-D2,IF(D2>K1,K1*2-D2,IF(D2>K1*2,K1*3-D2)))
    =IF(D2<=K1,K1-D2,"next nest")
    =IF(D2>K1,K1*2-D2,"next nest")
    =IF(D2>K1*2,K1*3-D2)
    =D2
    =K1*2
    =K1*3-D2
    ...

    That usually shows where the problem is and then I can fix it.

    .
     
    Matt G and koolaide like this.
  6. ac15

    ac15 Poster Extraordinaire Ad Free Member

    Posts:
    7,180
    Joined:
    May 9, 2005
    Location:
    CHICAGO, IL.
    Sorry I don’t have an answer to your question, but I just want to add that a person with a deep understanding of Excel in the workplace is worth their weight in gold. Such a great program, and so deep, but most of us (including me) barely scratch the surface of what it can do. I have seen countless examples of Excel gurus in our office literally saving hours of work with a few keystrokes or macros in Excel. If I had the time, I’d seriously learn a lot more about it.
     
  7. teleaddicted

    teleaddicted Tele-Holic

    Age:
    66
    Posts:
    612
    Joined:
    Nov 5, 2013
    Location:
    Roma Urbe
  8. uriah1

    uriah1 Telefied Gold Supporter

    Posts:
    21,114
    Joined:
    Feb 12, 2011
    Location:
    Around
    You might just want to setup different columns for optimal answers, easier then nested nests.

    column Aa = formula1
    column AB = formula2
    column ac = formula3
    etc

    Column ad = column aa & column ab &column ac

    If need be, set up another table to intepret finding(vlookup) and then multiply (aaa= 1x1, aba = 1x2,etc)
     
  9. ddewerd

    ddewerd Friend of Leo's Ad Free Member

    Age:
    59
    Posts:
    2,419
    Joined:
    Nov 14, 2003
    Location:
    Willow Springs, Great-State-of-Texas
    So did you ever figure it out?

    Cheers,
    Doug
     
  10. SparkleFart

    SparkleFart TDPRI Member

    Posts:
    66
    Joined:
    Jan 15, 2017
    Location:
    SE Alaska
    I agree--a final parenthesis here and I believe it matches the logic the OP described.
     
  11. ddewerd

    ddewerd Friend of Leo's Ad Free Member

    Age:
    59
    Posts:
    2,419
    Joined:
    Nov 14, 2003
    Location:
    Willow Springs, Great-State-of-Texas
    Yup, good catch. Like I said, too early and not enough coffee when I did that!

    =IF(D2<=K1,K1-D2,IF(D2>K1*2,k1*3-d2,K1*2-D2))

    Cheers,
    Doug
     
    Tommy Biggs likes this.
  12. moosie

    moosie Doctor of Teleocity Ad Free Member

    Age:
    63
    Posts:
    16,110
    Joined:
    Jul 18, 2010
    Location:
    Western Connecticut
    I haven't used Excel in a long time, but Google Sheets and most others highlight matching parens. Doesn't Excel do that? Meaning, you can move the cursor along the formula, and when you hit a paren, it'll show you the matching one. If it don't show on the last or first, then you're missing one, or have an extra.
     
IMPORTANT: Treat everyone here with respect, no matter how difficult!
No sex, drug, political, religion or hate discussion permitted here.


  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.