really hard excel formula

[gillian1552]

Hello.

I have a really hard excel formula and although i know what i want it to do in my head im not sure if this is possible.

i need column 3 to work out:-
if column 1 is 2 that equals figure in column 2 up to the amount of 10, if column 1 is 3 that equals figure in column 2 up to the amount of 15, if column 1 is 4 that equals figure in column 2 up to the amount of 20, if column 1 is 13 that equals figure in column 2 up to the amount of 65

also column 4 to work out:-
if column 1 is 2 that equals figure in column 2 if over 10, if column 1 is 3 that equals figure in column 2 if over 15, if column 1 is 4 that equals figure in column 2 if over 20, if column 1 is 13 that equals figure in column 2 if over 65

any help would be greatly appreciated     

[strollin]

First of all, you should use A, B, C, ... to talk about columns in Excel and numbers for rows so instead of saying "Column 1" use "Column A" or even better, refer to the actual cell as in A1 (column A, row 1).  That should make it easier to explain what you want.

I'm not exactly sure what you're asking for.  You want a formula that: if A1 has the value of 2, the value in C1 should be equal to the value in B1 up to the value of 10.  What happens if the value is greater than 10?

Is this what you want for the first formula?

=IF(A1=2,IF(B1<=10,B1,),IF(A1=3,IF(B1<=15,B1,),IF(A1=4,IF(B1<=20,B1,),IF(A1=13,IF(B1<=65,B1,)))))

If that works for you then the 2nd formula is pretty similar:

=IF(A1=2,IF(B1>10,B1,),IF(A1=3,IF(B1>15,B1,),IF(A1=4,IF(B1>20,B1,),IF(A1=13,IF(B1>65,B1,)))))

BTW, I usually get paid to write stuff like this so, in the future, you should attempt to write the formulas yourself.  If you can't get it to work then you can post what you wrote and forum members can help straighten out why your formula doesn't work.  If you can't write the formulas at all then you need to take a class, study on-line or pay someone to help you.  It's not right to come here and ask for formulas to be written for you.

[ferrico2]

The problem statement certainly is vague, but here's my attempt at a solution...

A1 - data - expecting values between 2 and 13 inclusive
B1 - data - Some other value whose 'significant thresholds' appear to be 5x A1
C1 - formula - "=MIN(5*A1,B1)"
D1 - formula - "=IF(B1>(5*A1),B1,"")"

There's no indication of fractional parts, or whether C1 or D1 should be blank if not 'in range'...

Yes, I presumed that A1's value is from a continuum between 2 and 13, and that C1 and D1 reflect the 'pattern' implied by the problem statement.  Otherwise, this has little meaning and a simple (large) table lookup would fill the need.

If this is "really hard", then Gillian1552 needs to hit the books...
"Press 'F1' for help" would be a good place to start...