$20 USD - MinIFS MaxIFS SmallIFS LargeIFS UDF

Re: $20 USD - MinIFS MaxIFS SmallIFS LargeIFS UDF

Ger,

Took a look and did some quick testing. Everything is exactly how I need it! Truly appreciated. Thank you for making my first experience with hired help at Ozgrid such a positive one.

Regards,

Adam

Re: $20 USD - MinIFS MaxIFS SmallIFS LargeIFS UDF

Ger,

Any idea why having my r1 range (the range to look for the small/min/max/large value) formatted as anything other than General or Number would cause these functions to error out? I have a sheet with values formatted as Currency. If I apply the small/largeifs formula on it, it will give 0. If I change the format to general, it produces the desired result.

I'm stumped, lol

Re: $20 USD - MinIFS MaxIFS SmallIFS LargeIFS UDF

Definitely strange behaviour going on...I've continued to play with this and it seems that if the format is Currency, or Accounting, the UDF's fail. Simple switch to General, or any of the other standard formats (text, percentage, number, date) and the UDF's calculate fine.

I should mention that it seems if you are using the udfs without criteria or criteria ranges (i.e. small of a range) then the formatting of the r1 range doesn't matter, currency/accounting works fine. BUT, if you add any sort of criteria to the UDF, then it errors out.

I'm at a loss, never experienced a value being pulled into VBA being dependent on the format of the cell it is taken from...:(

Re: $20 USD - MinIFS MaxIFS SmallIFS LargeIFS UDF

Seems like it has something to do with the Apply_Criteria_IFS section.

If I change :

My_Array(r_Count, 1) = .Cells(lLoop, r1.Column).Value
                My_Array(r_Count, 2) = .Cells(lLoop, r1.Column + Rel_Pos).Value

to

My_Array(r_Count, 1) = .Cells(lLoop, r1.Column).Value2
                My_Array(r_Count, 2) = .Cells(lLoop, r1.Column + Rel_Pos).Value2

then everything is ok...any insight (just for my understanding purposes)

Re: $20 USD - MinIFS MaxIFS SmallIFS LargeIFS UDF

Looks like we were posting at the same time.

Based on some reading I think it has to do with the .value vs .value2 difference in how VBA treats dates and currency.

http://fastexcel.wordpress.com/2011/11/

"[h=3]Range.Value[/h]This mostly gets the underlying value from the cell.
But if the cell is formatted as a date or currency then Range.Value converts the underlying value into a VBA date or currency variable before passing it to the VBA variable used in the assignment statement. Since VBA currency only has 4 decimal places this can cause a loss of accuracy. Suppose cell G4 has a value of 123.456789 formatted as currency. Then Range(“g4″).Value will retrieve a value of 123.4568 rather than the actual value!
"

Just wanted to make sure this thread was complete if anyone else ever stumbles on it.

Do you think this could be causing the issue?

Re: $20 USD - MinIFS MaxIFS SmallIFS LargeIFS UDF

Excellent, learn something new every day :)!

Definitely interesting regarding .value and .value2, something I wasn't aware of either.

As always, thanks for your time