If Formula??

[Guest]

I have 10 cells with different prices. I want to be able to automatically
calculate, and I am not sure of the formula that will allow me to do this. I
want the formula to automatically look at the list of prices and IF the price
is greater than the price in column A, then it will look in column B. It it
is not greater than column B, that price will be put in the cell with the
formula. If the price is not greater than column B, then it will move down
the remainding columns until it finds the price that is not greater than.
Any help with this would be GREATLY appreciated.

 

[Guest]

Hi Bambi,

Assuming your orig. search price is in A1
and the 10 prices are in A2:J2
please inform IF
1. Any possibility that there will be blank cell (no price) within A2:J2 ? .
2. Are the 10 prices arranged in which order (ascending, random, descending) ?

regards

 

[Guest]

Hi,

Not sure if this is what you want but this will sum every price in column A
that is less than the price in column B on the same row.

=SUMPRODUCT(--(A1:A10<B1:B10),B1:B10)

HTH
Jean-Guy

 

[Guest]

There will not be any blank cells and the order is ascending

Thanks--
Bambi

 

[Guest]

Bambi

first one: IF the price is greater than the price in column A, then it will
look in column B. If it is not greater than column B, that price will be put
in the cell with the formula.
second one : If the price is not greater than column B, then it will move
down
the remainding columns until it finds the price that is not greater than.

i have some confusion between the first and second one....

First one: if u want the nearest Price greater than or equal the search
price...
on A1 = search price
on A2:J2 columns of prices
=MIN(IF((A2:J2>=A1),A2:J2))
press ctrl-shft-enter

Second: if u want the least price which is NOT GREATER THAN THE search
price...
on A1 = search price
on A2:J2 columns of prices
=IF((A2:J2<=A1),A2:J2,"ALL PRICES ARE GREATER THAN YOUR SEARCH PRICE")
press ctrl-shft-enter

regards