Calculating ongoing if statements (>=,<=)

[Brapp2Smokin]

Hi I am trying to return weighted figured depending on the dollar value in specific cell.

I have attached a test file.

The formula works up to the second IF statement then discontinues working.

Any help would be greatly appreciated.


Thanks!

[AliGW]

In this IF statement, you need to start with your low values and work upwards. However, a LOOKUP or VLOOKUP would be more efficient:

https://www.techonthenet.com/excel/formulas/lookup.php

https://exceljet.net/excel-functions...ookup-function

[Brapp2Smokin]

I have added a solution file.

If anyone sees an issue with this an/or a more efficient solution I am all ears.

Thanks!

[AliGW]

I gave you two links with two possible approaches, both more efficient. You have not acknowledged my post, and just posted the exact same file again. What was it about the two solutions I suggested that you couldn't manage to follow? Where in the new workbook are you attempts at implementing them?

[jtakw]

Hi,

@AliGW, OP made a working IF formula in Post#3.

Your IF statement can be shortened quite a bit, no need for all the AND functions:

Please Login or Register  to view this content.

And, here's a LOOKUP alternative:

Please Login or Register  to view this content.

Refer to attached file for details.

[AliGW]

I saw no LOOKUP or VLOOKUP in the file in post #3 - sorry that I missed it. It looked the same to me as the first file posted.

[Brapp2Smokin]

Hi AliGW,

my apologies! The articles you posted are great source of information. Is the Vlookup function as accurate as the if/and statements?

@jtakw - Thank you for the response.


Thanks!

[AliGW]

Why wouldn't it be? You are just setting the parameters in a table or lookup array instead of an IF statement.

I STRONGLY suggest you use the articles to convert your IF statament first into a LOOKUP formula and then VLOOKUP (where you will first of all need to set up the lookup table).

Have you actually tried either? If you had, you would see that they will give you the same (correct) answers.

Both of my suggestions are a more EFFICIENT way of doing the same thing: that has nothing to do with accuracy.

[jtakw]

Is the Vlookup function as accurate as the if/and statements?

@jtakw - Thank you for the response.

Regardless of what Function(s) are used in a formula, as long as it produces the correct results, then it's "accurate", there's no such thing as one function being "more" accurate than another...

The difference or question is thou, which function to use, or which way to set up the formula, so that it is the "more" efficient way to solve the problem.

If you're problem is "Solved", please mark the thread as such.

Add rep would be appreciated, click the "Star" at the lower left corner of the posts you found helpful.