Excel formula help

00Seven · 05-30-2005, 09:51 AM

Please see the Attachment, and open as an excel file.
Two formula is selecting the lowest price and then selecting the vendor of the lowest price.

So far if there is no price for one of the vendors for eg, the formula does not work, and if I put a zero in, it selects the zero as best price and vendor.

How can I tell the formula to select the best price but if it shows blank or zero to goto the next lowest price?

Hope I have explained it ok

**ReeKorl** · 05-31-2005, 05:05 AM

I've gotta say that a custom function would do the trick much better than cell-formulising it. It's ok with 2 vendors, painfully messy with three and almost impossible to code with 4 if you just code it in the cell. With three vendors, you'll need 7 IF statements (nested to level three) and with 4 vendors, you need a whopping 15 IF statements in the formula.

Give me a couple of hours and I'll try to make a function for you to put in, but here's some example pseudo-code for doing it the cell-formulaic way.

Code:

IF 1 IS BLANK
    IF 2 IS BLANK
        IF 3 IS BLANK
            "No Vendors"
            V3
        IF 3 IS BLANK
            V2
            MIN: V2, V3
    IF 2 IS BLANK
        IF 3 IS BLANK
            V1
            MIN: V1, V3
        IF 3 IS BLANK
            MIN: V1, V2
            MIN: V1, V2, V3

btw, the IF BLANK would also include the value being 0.

**ReeKorl** · 05-31-2005, 07:40 AM

OK, turns out I practically did the same in a VBA formula, but it's easier to read and you can use it to find the minimum of any three, non zero cells - all you need to do is tell it which cells.

Updated file is attached - not sure why, but it seems to need the ROUND function to allow it to compare the min value to the absolute values - likely it's just a rounding error in the computer on turning decimal to binary and back.

Hope this helps!

00Seven · 05-31-2005, 09:11 AM

Thanks, just got in now and opening it up. Interesting name "ReeKorl", does it mean anything, I am assuming so.

S

Where in the main VBA script shall I place this script, in the front end or inside the other main one?

**ReeKorl** · 05-31-2005, 09:32 AM

As long as the code is intact (ie from Function... to ...End Function) you can put it anywhere in the existing module (as long as it isn't inside the sub), so it'll probably be best to copy the text (except for the Option Explicit line), and paste it all after the "End Sub" in the existing module.

As a matter of fact, ReeKorl doesn't actually mean anything in itself - but it has evolved partially as a joke and partially as a description of me. It's the literal pronunciation of the word "recall". Basically, I have a very good memory for numerics, technical details and strategies/tactics, etc, but ask me what I had for breakfast and I'm lost.

I've also found it works very well as an online handle, as I haven't come across anyone in over 8 years who uses the same name.

00Seven · 05-31-2005, 10:20 AM

It works very well. It sure is a complicated. I have been looking up some VBA and reasearching, but Rome was not built in a day.

When there is no value in all three I get a # VALUE! error. But I think I can live with that and play around with it.

Thanks again ReeKorl

**ReeKorl** · 05-31-2005, 10:28 AM

No problem.

The #VALUE! can be avoided easily. In the cell with the formula, simply put this function:

Code:

=IF(ISERROR(ROUND(ls3(E9,F9,G9),2)),"",ROUND(ls3(E9,F9,G9),2))

Note that the text in blue is the original fomula. This says if the value of the formula is an error, put a blank, otherwise put the actual value.

00Seven · 05-31-2005, 11:09 AM

Thanks again.

Is programming your specialty or are you an all round Computer wizard?

**ReeKorl** · 06-01-2005, 01:30 AM

Glad to have helped!

All round normally, but I've worked for the past 8 months programming excel spreadsheets to calculate pensions for a large company, so I've got a lot of recent experience with excel.

05-31-2005, 05:05 AM	#2 (permalink)
ReeKorl Superhuman Computer Join Date: Mar 2005 Location: 6th Circle, The City of Dis, Hell Posts: 1,610 OS: WinXP Pro SP2 My System	I've gotta say that a custom function would do the trick much better than cell-formulising it. It's ok with 2 vendors, painfully messy with three and almost impossible to code with 4 if you just code it in the cell. With three vendors, you'll need 7 IF statements (nested to level three) and with 4 vendors, you need a whopping 15 IF statements in the formula. Give me a couple of hours and I'll try to make a function for you to put in, but here's some example pseudo-code for doing it the cell-formulaic way. Code: IF 1 IS BLANK IF 2 IS BLANK IF 3 IS BLANK "No Vendors" V3 IF 3 IS BLANK V2 MIN: V2, V3 IF 2 IS BLANK IF 3 IS BLANK V1 MIN: V1, V3 IF 3 IS BLANK MIN: V1, V2 MIN: V1, V2, V3 btw, the IF BLANK would also include the value being 0. __________________ "Sorry, the number you have dialled is imaginary. Please rotate dial by 90 degrees and try again."

05-31-2005, 09:11 AM	#4 (permalink)
00Seven Member Join Date: May 2005 Location: South Carolina Posts: 38 OS: WinXP	Thanks, just got in now and opening it up. Interesting name "ReeKorl", does it mean anything, I am assuming so. S Where in the main VBA script shall I place this script, in the front end or inside the other main one? Last edited by 00Seven; 05-31-2005 at 09:16 AM.

05-31-2005, 09:32 AM	#5 (permalink)
ReeKorl Superhuman Computer Join Date: Mar 2005 Location: 6th Circle, The City of Dis, Hell Posts: 1,610 OS: WinXP Pro SP2 My System	As long as the code is intact (ie from Function... to ...End Function) you can put it anywhere in the existing module (as long as it isn't inside the sub), so it'll probably be best to copy the text (except for the Option Explicit line), and paste it all after the "End Sub" in the existing module. As a matter of fact, ReeKorl doesn't actually mean anything in itself - but it has evolved partially as a joke and partially as a description of me. It's the literal pronunciation of the word "recall". Basically, I have a very good memory for numerics, technical details and strategies/tactics, etc, but ask me what I had for breakfast and I'm lost. I've also found it works very well as an online handle, as I haven't come across anyone in over 8 years who uses the same name. __________________ "Sorry, the number you have dialled is imaginary. Please rotate dial by 90 degrees and try again." Last edited by ReeKorl; 05-31-2005 at 09:33 AM.

05-31-2005, 10:28 AM	#7 (permalink)
ReeKorl Superhuman Computer Join Date: Mar 2005 Location: 6th Circle, The City of Dis, Hell Posts: 1,610 OS: WinXP Pro SP2 My System	No problem. The #VALUE! can be avoided easily. In the cell with the formula, simply put this function: Code: =IF(ISERROR(ROUND(ls3(E9,F9,G9),2)),"",ROUND(ls3(E9,F9,G9),2)) Note that the text in blue is the original fomula. This says if the value of the formula is an error, put a blank, otherwise put the actual value. __________________ "Sorry, the number you have dialled is imaginary. Please rotate dial by 90 degrees and try again."

06-01-2005, 01:30 AM	#9 (permalink)
ReeKorl Superhuman Computer Join Date: Mar 2005 Location: 6th Circle, The City of Dis, Hell Posts: 1,610 OS: WinXP Pro SP2 My System	Glad to have helped! All round normally, but I've worked for the past 8 months programming excel spreadsheets to calculate pensions for a large company, so I've got a lot of recent experience with excel. __________________ "Sorry, the number you have dialled is imaginary. Please rotate dial by 90 degrees and try again."

05-31-2005, 10:20 AM	#6 (permalink)
00Seven Member Join Date: May 2005 Location: South Carolina Posts: 38 OS: WinXP	It works very well. It sure is a complicated. I have been looking up some VBA and reasearching, but Rome was not built in a day. When there is no value in all three I get a # VALUE! error. But I think I can live with that and play around with it. Thanks again ReeKorl

05-31-2005, 11:09 AM	#8 (permalink)
00Seven Member Join Date: May 2005 Location: South Carolina Posts: 38 OS: WinXP	Thanks again. Is programming your specialty or are you an all round Computer wizard?