Complex formula is calculating random cells and skipping others.

Rytis Karalius

Hi everybody,

So this will be a long read, but i hope somebody will help me solve this craziness, that already ate 2 days of my life.

So i have this formula:

=IFERROR(IF(VLOOKUP([Seller]1; {Range Seller | Bonus}; 2) = 1; IF(VLOOKUP([Sum]1; {Bonusai Range 1}; 2) < 1; [Sum]1 * VLOOKUP([Sum]1; {Bonusai Range 1}; 2); VLOOKUP([Suma (be PVM)]1; {Bonusai Range 1}; 2)); IF(VLOOKUP([Seller]1; {Techninis Range Partneris | Bonusas}; 2) = 2; IF(VLOOKUP([Sum]1; {Bonusai Range 2}; 2) < 1; [Sum]1 * VLOOKUP([Sum]1; {Bonusai Range 2}; 2); VLOOKUP([Suma (be PVM)]1; {Bonusai Range 2}; 2)); IF(VLOOKUP([Seller]1; {Range Seller | Bonus}; 2) = 3; IF(VLOOKUP([Sum]1; {Bonusai Range 3}; 2) < 1; [Sum]1 * VLOOKUP([Suma (be PVM)]1; {Bonusai Range 3}; 2); VLOOKUP([Sum]1; {Bonusai Range 3}; 2)); IF(VLOOKUP([Seller]1; {Range Seller | Bonus}; 2) = 0; 0)))); 0)

Which does the following -

VLOOKUP "Seller" cell and check what Bonus level it has on another sheet ({Range Seller | Bonus}).

Then calculate the bonus based on the level . If level 1... 2... 3... Calculate according to another sheet.

Calculation is pretty simple - IF value / multiplier ">1" then give the "value/multiplier" as a result, if "value/multiplier" is "<1" - then multiply sum * value/multiplier.

This process is repeated 3 times for each level (1, 2, 3).

If the level is set to "0" - Print "0" as a result. All formula is covered with "IFERROR" - print "0".

PROBLEM:

Some cells get calculated, while others - do not.

There are no spelling/grammar/mistype problems because for testing purposes i just copy-pasted "Sellers".

There are no relations with SUM / Name used / Bonus level size or what ever to cause this error.

Seems like an absolute randomness - Cells are getting calculated or no when the first time i save the sheet. After that - only the same cell is calculated. It won't be calculated even if i switch rows.

After some testing i found out, that ALL cells, which are not calculated correctly are randomly treated as having Bonus Level "0" (even though it's 1, 2, or 3), i found this because i've set last condition "If Bonus level is 0, print "TEST-IF-ZERO".

=IFERROR(IF(VLOOKUP([Seller]1; {Range Seller | Bonus}; 2) = 1; IF(VLOOKUP([Sum]1; {Bonusai Range 1}; 2) < 1; [Sum]1 * VLOOKUP([Sum]1; {Bonusai Range 1}; 2); VLOOKUP([Suma (be PVM)]1; {Bonusai Range 1}; 2)); IF(VLOOKUP([Seller]1; {Techninis Range Partneris | Bonusas}; 2) = 2; IF(VLOOKUP([Sum]1; {Bonusai Range 2}; 2) < 1; [Sum]1 * VLOOKUP([Sum]1; {Bonusai Range 2}; 2); VLOOKUP([Suma (be PVM)]1; {Bonusai Range 2}; 2)); IF(VLOOKUP([Seller]1; {Range Seller | Bonus}; 2) = 3; IF(VLOOKUP([Sum]1; {Bonusai Range 3}; 2) < 1; [Sum]1 * VLOOKUP([Suma (be PVM)]1; {Bonusai Range 3}; 2); VLOOKUP([Sum]1; {Bonusai Range 3}; 2)); IF(VLOOKUP([Seller]1; {Range Seller | Bonus}; 2) = 0; "TEST-IF-ZERO")))); 0)

POSSIBLE SOLUTION:

The only thing that comes to my mind - is that formula is too overcrowded with vlookups, and i need to either simplify it, or simplify it :) Any ideas on what's wrong with this mess?

Paul Newcome

Try looking for an exact match in the VLOOKUP that pulls the bonus number.

Ben Goldblatt

Hi @Rytis Karalius ,

In reviewing your formula, I suspect that the issue may have to do with not having a match_type included with any of the VLOOKUP statements. Even though it's listed as an optional argument, it's possible that adding in the “false” match_type will help to ensure they’re looking for exact matches.

More information on working with the VLOOKUP function can be found in our Help article here. Do let us know if adding in the false match_type doesn't resolve the issue and we'll be happy to look into this further. If the issue persists, I also recommend reaching out to our Support Team via https://help.smartsheet.com/contact/smartsheetapp as they will be the best resource for troubleshooting.

Thanks,

Ben

Paul Newcome

I agree that one of the first steps would be to enter a Match Type in your VLOOKUP functions since it is defaulted to an approximate match when it is left out.

Rytis Karalius

Well, i thought the match type should be left default as "true", because what we are searching for - is the approximate values of "Bonus", then adding bonus as a fixed number or as a multiplier based on bonus size, i cannot change it to "False", because there are no exact matches.

Paul Newcome

Are you able to show a screenshot of the source data for the VLOOKUP?

Rytis Karalius

Sure thing, @Paul Newcome, thank you for helping me with this.

I did post it in the first post, but probably did not make it clear.

Sums and Bonuses are calculated in this table:

VLOOKUP takes Sum from first table and is searching for approximate values in second table (this one)

, based on range, that is determined by "Bonus level". It first checks wether bonus level is "1" in another sheet, then I.E. If SUM is between 0 - 200 it gives "0" as a bonus. If sum is 200 - 700 it gives 45 as a bonus and so on. If sum is 2900 - 4000 - it multiplies 0.03 * Sum from first table.

If the Bonus is level 2 - formula does the same with Bonus Level 2 range values.

Paul Newcome

Try looking for an exact match in the VLOOKUP that pulls the bonus number.

Rytis Karalius

https://community.smartsheet.com/discussion/comment/254393#Comment_254393

Holy Cows! This actually worked! Thank you very much.

All bonuses are calculated perfectly now!

Paul Newcome

Happy to help. 👍️