Forum Discussion
Solved
Excel Formula Problem
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, wh...
- 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_1119067863/
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