# Any excel guru's Help needed.

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

1. ### koolaideTele-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)))

Thanks all for looking,
Jim

2. ### Tommy BiggsFriend of Leo's

Posts:
2,220
Joined:
Jun 17, 2010
Location:
Northern NJ
(K1-D2)...((K1*2)-D2)

koolaide likes this.
3. ### tjniesTele-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. ### ddewerdFriend of Leo'sAd 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. ### jvin248Poster 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. ### ac15Poster ExtraordinaireAd 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.

Age:
66
Posts:
612
Joined:
Nov 5, 2013
Location:
Roma Urbe
koolaide likes this.
8. ### uriah1TelefiedGold 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. ### ddewerdFriend of Leo'sAd 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. ### SparkleFartTDPRI Member

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

11. ### ddewerdFriend of Leo'sAd 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. ### moosieDoctor of TeleocityAd 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.