Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
paul
 
Posts: n/a
Default excel 2003 help comparing prices for lowest

I am trying to compare prices from various customers to find who have the
lowest prices.


Company A Company B Company C
Company D


B4 E4 H4
K4 Result
$3.00 $4.00 $2.00 $10.00 company B



I wrote this formula and go this result

=IF(B4K4,IF(B4H4,IF(B4E4,IF(K4B4,IF(K4E4,E4,K 4),H1),B1)),E1)

but it is not giving me the correct answer always, I know i am missing
something but not sure where to add the correct formula. Also if the cell is
blank I want to just disregard the cell. If 2 or more customers have the
same price I want to display that information. I think this is
"concatenate".
any help would be appreciated


  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Miguel Zapico
 
Posts: n/a
Default excel 2003 help comparing prices for lowest

Use MIN instead: MIN(range_with_values)
In order to know the number of companies with that price, use COUNTIF, to
find the value from the MIN formula in the range with the prices.
--
It is nice to be important, but it is more important to be nice


"paul" wrote:

I am trying to compare prices from various customers to find who have the
lowest prices.


Company A Company B Company C
Company D


B4 E4 H4
K4 Result
$3.00 $4.00 $2.00 $10.00 company B



I wrote this formula and go this result

=IF(B4K4,IF(B4H4,IF(B4E4,IF(K4B4,IF(K4E4,E4,K 4),H1),B1)),E1)

but it is not giving me the correct answer always, I know i am missing
something but not sure where to add the correct formula. Also if the cell is
blank I want to just disregard the cell. If 2 or more customers have the
same price I want to display that information. I think this is
"concatenate".
any help would be appreciated



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
paul
 
Posts: n/a
Default excel 2003 help comparing prices for lowest

Thank you Miguel
MIN worked I have to work on the countif now.


"Miguel Zapico" wrote in message
...
Use MIN instead: MIN(range_with_values)
In order to know the number of companies with that price, use COUNTIF, to
find the value from the MIN formula in the range with the prices.
--
It is nice to be important, but it is more important to be nice


"paul" wrote:

I am trying to compare prices from various customers to find who have the
lowest prices.


Company A Company B Company C
Company D


B4 E4
H4
K4 Result
$3.00 $4.00 $2.00 $10.00 company B



I wrote this formula and go this result

=IF(B4K4,IF(B4H4,IF(B4E4,IF(K4B4,IF(K4E4,E4,K 4),H1),B1)),E1)

but it is not giving me the correct answer always, I know i am missing
something but not sure where to add the correct formula. Also if the cell
is
blank I want to just disregard the cell. If 2 or more customers have the
same price I want to display that information. I think this is
"concatenate".
any help would be appreciated





  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Miguel Zapico
 
Posts: n/a
Default excel 2003 help comparing prices for lowest

Maybe an example could help. If your data is in A3:D3, and you put the
formula "=MIN(A3:D3)" in the cell A5, then the formula for the countif should
be "=COUNTIF(A3:D3,A5)"

--
It is nice to be important, but it is more important to be nice


"paul" wrote:

Thank you Miguel
MIN worked I have to work on the countif now.


"Miguel Zapico" wrote in message
...
Use MIN instead: MIN(range_with_values)
In order to know the number of companies with that price, use COUNTIF, to
find the value from the MIN formula in the range with the prices.
--
It is nice to be important, but it is more important to be nice


"paul" wrote:

I am trying to compare prices from various customers to find who have the
lowest prices.


Company A Company B Company C
Company D


B4 E4
H4
K4 Result
$3.00 $4.00 $2.00 $10.00 company B



I wrote this formula and go this result

=IF(B4K4,IF(B4H4,IF(B4E4,IF(K4B4,IF(K4E4,E4,K 4),H1),B1)),E1)

but it is not giving me the correct answer always, I know i am missing
something but not sure where to add the correct formula. Also if the cell
is
blank I want to just disregard the cell. If 2 or more customers have the
same price I want to display that information. I think this is
"concatenate".
any help would be appreciated






  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default excel 2003 help comparing prices for lowest

See my post in for an approach that takes ties into account:

http://www.mrexcel.com/board2/viewtopic.php?t=72823


paul wrote:
I am trying to compare prices from various customers to find who have the
lowest prices.


Company A Company B Company C
Company D


B4 E4 H4
K4 Result
$3.00 $4.00 $2.00 $10.00 company B



I wrote this formula and go this result

=IF(B4K4,IF(B4H4,IF(B4E4,IF(K4B4,IF(K4E4,E4,K 4),H1),B1)),E1)

but it is not giving me the correct answer always, I know i am missing
something but not sure where to add the correct formula. Also if the cell is
blank I want to just disregard the cell. If 2 or more customers have the
same price I want to display that information. I think this is
"concatenate".
any help would be appreciated


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Data from Excel 2000 Worksheet with external links is not displayed when opened in Excel 2003 Rich Rodberg Links and Linking in Excel 1 October 21st 05 07:53 AM
Excel Re-calculation 2000 compared to 2003 tonymitchell Excel Discussion (Misc queries) 1 August 3rd 05 04:22 PM
Excel 2003 crashes loading excel files created Excel 2000 Jeff Lewin Australia Excel Discussion (Misc queries) 0 June 27th 05 04:20 AM
Comparing a value to a list of values does not work in Excel 2003. RagDyer Excel Worksheet Functions 3 March 29th 05 02:12 AM
sharing/using/saving Excel 2002 files in Excel 2003 maze2009 Excel Discussion (Misc queries) 0 January 20th 05 08:27 PM


All times are GMT +1. The time now is 03:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"