Conditional Formula

D

DeeExpus

Hi all.
I have a spreadsheet which is basically a Hire Rate price list. I have
5 columns (1-6 week, 7 - 16 week etc etc) which I need to display the
cotents of. form looks like this:

HIRE PERIOD
5

QTY DISC 1-6W 7-15W 16-31W TOTAL
3 bla bla £1.50 £1.00 £0.50 ???


sooo, I need the total to calculate the correct column*the quantity
basedon the amount of weeks in the Hire Period Cell.

Any Clue?

Cheers
Andy
 
B

Bob Phillips

=INDEX(C2:E2,1,SUMPRODUCT(--(--LEFT(C1:E1,FIND("-",C1:E1)-1)<=5),--(--MID(C1
:E1,FIND("-",C1:E1)+1,FIND("W",C1:E1)-FIND("-",C1:E1)-1)>=5),COLUMN(C1:E1))-
MIN(COLUMN(C1:E1))+1)*A2

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
D

DeeExpus

Thanks for the reply and the solution Bob. It didnt work for me
unfortunately.

Forgive me if I didnt explain myself too well, or If I have
misinterpreted, but just to clarify:

I have a cell ($I$10) in which a user will input the number of weeks
they want to hire furniture for, between 1 - 52.

There are then Hire Rates separated into 5 columns becoming cheaper per
week depending on the Hire Period (ie. 6 wk minimum, 7-15 wk, 16-31 wk,
32-51 wk, 52 wk+ these are the column headers). Directly under those
headers are cells H16:L16 each with hire rates which are fixed. There
is also a quantity cell (A16) which is variable by the user, and
finally the total cell (which is obviously the one I need the formula
for (N16))

What I need to do is have the user enter a hire period and the formula
calculate which column to use based on that hire period to calculate
the total cost of the hire ie A16*H16 (or I16, or J16 etc etc)

Or I could just email you the form!!!

heh

Thanks again for your help.
Andy
 
B

Bob Phillips

Andy,

If you make a small change to you headings, namely
1-6 wk
7-15 wk
16-31 wk
32-51 wk
52-99 wk

you can then use that formula, adapted to your data,

=INDEX($H16:$L16,1,SUMPRODUCT(--(--LEFT($H$15:$L$15,FIND("-",$H$15:$L$15)-1)
<=$I$10),
--(--MID($H$15:$L$15,FIND("-",$H$15:$L$15)+1,FIND("
wk",$H$15:$L$15)-FIND("-",$H$15:$L$15)-1)>=$I$10),COLUMN($H$15:$L$15))
-MIN(COLUMN($H$15:$L$15))+1)*$A16

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)
 
Top