This forum uses cookies
This forum makes use of cookies to store your login information if you are registered, and your last visit if you are not. Cookies are small text documents stored on your computer; the cookies set by this forum can only be used on this website and pose no security risk. Cookies on this forum also track the specific topics you have read and when you last read them. Please confirm whether you accept or reject these cookies being set.

A cookie will be stored in your browser regardless of choice to prevent you being asked this question again. You will be able to change your cookie settings at any time using the link in the footer.


Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Geeks : Excel Help Required
#1
Hi all - work during this festive period means I have tedious but essential tasks to complete..... over to you.....

I have 2 columns A and B, they contain a price from supplier X and Supplier Y.

Quite often, one supplier does not supply a product that the other does (and vice-versa)

I need a formula to put into column C to pick the lowest price from either A or B, but exclude the column/suplier if its zero (no price from that supplier)

Im currently stuck as its picking up the lowest price as Zero which is not what I need. (was using the MIN function)

I guess its a Min function but designed to exclude any cell with Zero in it.

Any help welcome.......

Example

A                  B                            C
£100             £110                      Ã‚£100
£0.00            Ã‚£50.00                   £50.00
£25.00          Ã‚£0.00                     £25.00
Reply
#2
=if(min(A1,B1)=0,Max(A1,B1),Min(A1,B1))
Reply
#3
Better still-

Code:
=small(A1:B1,countif(A1:B1,0)+1)

This will work with more than 2 suppliers unlike my original answer.
Reply
#4
Both work - cheers dude!  ;D
Reply
#5
"General Sparks" Wrote:Better still-

Code:
=small(A1:B1,countif(A1:B1,0)+1)

This will work with more than 2 suppliers unlike my original answer.

I prefered the one with the smiley faces  ;D ( :B)
Reply
#6
"Laggingbehind" Wrote:I prefered the one with the smiley faces  ;D ( :B)

If only excel let me use smileys my working day would be just that little bit brighter, sigh.   :B
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)