SOLVED

Contributor

# 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, 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

# Re: Excel Formula Problem

Try this in I5 and copy down:
=IF((\$E5<=5)*(\$E5>0)*(\$D5=0),\$H5*(1-\$I\$2),0)

# Re: Excel Formula Problem

Works perfectly. Thank you!

# Re: Excel Formula Problem

You're quite welcome, that's good to hear.

# Re: Excel Formula Problem

@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?

# Re: Excel Formula Problem

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).