Pulling my hair out, need some help building a formula

[Guest]

I've created a work book with 4 pages.
1st one has a form that when data (number) is entered in one box, anohter
box needs to show a dollar figure based on data linked from one of the 3
other sheets.
The calculation needs to sum a total and return that value to the form (1st)
page.
ex. 1st box can only be 1 to 4: 4 entered 2nd box : 3rd box and 4th box
returns calculated amount from one of 3 other sheets.

Data entered on 2nd line must be between 5-20: 2nd 3rd & 4th box: returns
calculated amount as above.

Data entered on 3rd line must be between 21-84: 2nd 3rd & 4th box: returns
calculated amount as above.

Data entered on 4th line must be between 85-340: (as above)

Data entered on 5th line must be between 341-1364: (as above)

Data entered on 6th line must be between 1365-5460: (as above)

I have attempted to use just about every function.

I can get a value to return that just adds the range of values, regardless
of the value input in the 1st box what i can't seem to figure out how to ask
in formula for a value to be returned that is calculated (dependant) on the
value entered in the first box.

Any help / guidance would be greatfully appreciated.

John

 

[OzziJC]

You will probably need some nested IF functions....
Try something like

=if(a1=1,....,if(a2=2,....,if(a3=3,....,etc...)))

JC

 

[Guest]

OzziJC - thank you for your reply.

For my clarification : can a formula (possibly sum) be used to add the
number of value represented by =1 (or 25 or 340 or 1002 for example) or would
I need to input value to be returned for each value input.

Ex. if 4 is input in first box, does the formula need to be
=if(a1=1,0,if(a1=2,1,if(a1=3,2 ... ))) -- or --
if(a1=3,sum(sheet$2$a1:a3))

where sheet$2$a1:a3 represents the ref. to the value(s) to be calculated
and returned on sheet1 where the user would input data?

Thanks again,

John