SOLVED

Excel Formula Problem

%3CLINGO-SUB%20id%3D%22lingo-sub-2194613%22%20slang%3D%22en-US%22%3EExcel%20Formula%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2194613%22%20slang%3D%22en-US%22%3E%3CP%3EProblem%20in%20Column%20I%20when%20Column%20D%20is%20filled%20with%20a%20quote%20number.%20The%20formula%20works%20for%20the%20first%20few%20rows%20but%20then%20it%20shows%20a%20discount%20when%20it%20shouldn't.%20See%20the%20attached%20worksheet%20example.%26nbsp%3B%3C%2FP%3E%3CP%3EWhy%2C%20when%20I%20drag%20the%20formula%20through%20the%20rows%20to%20copy%20it%2C%20does%20it%20not%20yield%20consistent%20results%3F%20Please%20explain.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2194613%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2195124%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formula%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2195124%22%20slang%3D%22en-US%22%3ETry%20this%20in%20I5%20and%20copy%20down%3A%3CBR%20%2F%3E%3DIF((%24E5%26lt%3B%3D5)*(%24E5%26gt%3B0)*(%24D5%3D0)%2C%24H5*(1-%24I%242)%2C0)%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Contributor

Problem in Column I when Column D is filled with a quote number. The formula works for the first few rows but then it shows a discount when it shouldn't. See the attached worksheet example. 

Why, when I drag the formula through the rows to copy it, does it not yield consistent results? Please explain.

5 Replies
best response confirmed by Awolbert (Contributor)
Solution
Try this in I5 and copy down:
=IF(($E5<=5)*($E5>0)*($D5=0),$H5*(1-$I$2),0)
Works perfectly. Thank you!
You're quite welcome, that's good to hear.

@JMB17  How do I learn to better understand the formulas? I understand the function of the formulas provided by the community (thank you to many of you who helped) but going forward I would benefit by learning how the formulas relate to the functions I needed. What tutorials do you recommend? I do know how to create simple formulas, but nothing to the degree that you and the community offered. Do you have advice?

When I started, I obtained John Walkenbach's (Mr. Spreadsheet) book, Excel 2003 Formulas. While there are subsequent editions to update it for later Excel versions, I don't think he is writing anymore and I don't see anything for the newest Excel versions (which is too bad, I personally really liked how he explained things). It looks like there is a Excel 2016 Formulas, which I think would still be helpful as the functions it would cover are still in use (technically written by Alexander and Kusleika, who are quite knowledgeable, but it appears to be a continuation of John's work and I would guess a lot of his material).

https://www.amazon.com/Excel-2016-Formulas-Spreadsheets-Bookshelf/dp/1119067863/ref=asc_df_111906786...

I would also recommend taking a look at Chip Pearson's site. It has lots of formula examples, vba, and downloadable content.
http://www.cpearson.com/Excel/MainPage.aspx

And, I usually also point people to this white paper on using sumproduct for multiple condition counts/sums. Even if it doesn't make sense initially, it may be helpful in understanding some of the formulas you may see using it (and array math in general).

http://xldynamic.com/source/xld.SUMPRODUCT.html