Formula Help!!!!
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Formula Help!!!!

  1. #1
    Guest

    Default

     

    I have a spreadsheet of formula's I need help with, I assume if I figure out even one column's worth of formula, then I can perhaps link them all together to get a successful spreadsheet.

    if anyone is available to look at my spreadsheet and give me one on one advice, feel free to email me.

    I have columns A thru S -
    Each column needs an "IF" type formula.

    i.e. Column A is :

    <120= +x
    121-140= +2x
    141-150= +3x
    151-170= +4x
    171-180= +5x
    181-200= +6x
    201-220= +12x
    221-250= +20x
    251+= +40x

    other columns are very similar, the end result I HOPE will be pricing in the "t" column. Lets say this is the shipping column. The weight will give the results (IF 171-200 = 6*x) X is a constant throughout the whole spreadsheet, so that with ONE change, the entire price list can be altered.
    Perhaps (IF 171-200 = 6*A1) ( I could introduce the value of x in cell A1 and move everything over.

    I have some more complicated formula issues later on in the spreadsheet, i.e. if there is text entered in column Q then columns K,L,M,N, are to be ignored.

    Any help would be appreciated, how to enter multiple "ifs" to a column of cells (I think perhaps sending the results to sheet 2 of the workbook might be better? ) However seems logical..

    ANY ideas would be great.. I'm really at a loss on this.



  2. #2
    New Member
    Join Date
    Feb 2002
    Location
    State? Insanity! :)
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I don't know why it entered that post as anonymous.
    Please feel free to email me if you'd like to see a copy of the spreadsheet to offer any suggestions..
    THANK you..

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,809
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-03-09 00:19, Anonymous wrote:

    I have a spreadsheet of formula's I need help with, I assume if I figure out even one column's worth of formula, then I can perhaps link them all together to get a successful spreadsheet.

    if anyone is available to look at my spreadsheet and give me one on one advice, feel free to email me.

    I have columns A thru S -
    Each column needs an "IF" type formula.

    i.e. Column A is :

    <120= +x
    121-140= +2x
    141-150= +3x
    151-170= +4x
    171-180= +5x
    181-200= +6x
    201-220= +12x
    221-250= +20x
    251+= +40x

    other columns are very similar, the end result I HOPE will be pricing in the "t" column. Lets say this is the shipping column. The weight will give the results (IF 171-200 = 6*x) X is a constant throughout the whole spreadsheet, so that with ONE change, the entire price list can be altered.
    Perhaps (IF 171-200 = 6*A1) ( I could introduce the value of x in cell A1 and move everything over.

    I have some more complicated formula issues later on in the spreadsheet, i.e. if there is text entered in column Q then columns K,L,M,N, are to be ignored.

    Any help would be appreciated, how to enter multiple "ifs" to a column of cells (I think perhaps sending the results to sheet 2 of the workbook might be better? ) However seems logical..

    ANY ideas would be great.. I'm really at a loss on this.
    Wampcatt,

    I was going to ask: Who are you?

    When posting this, you were not logged on, whence the zillionst Anonymous.

    Back to your question.

    Enter X-value in an empty cell, in a sheet which you could name Admin. Select this cell, go to the Name Box on the Formula Bar, type, e.g., Price, and enter.

    Use the following formula to compute the pricing, which you enter in T2.

    =VLOOKUP(A2,{0,1;121,2;141,3;151,4;171,5;181,6;201,12;221,20;251,40},2)*Price

    where A2 houses a weight.

    Aladin

  4. #4
    Guest

    Default

    On 2002-03-09 00:19, Anonymous wrote:

    I have a spreadsheet of formula's I need help with, I assume if I figure out even one column's worth of formula, then I can perhaps link them all together to get a successful spreadsheet.

    if anyone is available to look at my spreadsheet and give me one on one advice, feel free to email me.

    I have columns A thru S -
    Each column needs an "IF" type formula.

    i.e. Column A is :

    <120= +x
    121-140= +2x
    141-150= +3x
    151-170= +4x
    171-180= +5x
    181-200= +6x
    201-220= +12x
    221-250= +20x
    251+= +40x

    other columns are very similar, the end result I HOPE will be pricing in the "t" column. Lets say this is the shipping column. The weight will give the results (IF 171-200 = 6*x) X is a constant throughout the whole spreadsheet, so that with ONE change, the entire price list can be altered.
    Perhaps (IF 171-200 = 6*A1) ( I could introduce the value of x in cell A1 and move everything over.

    I have some more complicated formula issues later on in the spreadsheet, i.e. if there is text entered in column Q then columns K,L,M,N, are to be ignored.

    Any help would be appreciated, how to enter multiple "ifs" to a column of cells (I think perhaps sending the results to sheet 2 of the workbook might be better? ) However seems logical..

    ANY ideas would be great.. I'm really at a loss on this.


    1.in cell A1 put value of X
    2.in column A beginig cell A2 put eny data
    3. in cell B2 write/copy formula :
    =CHOOSE(MATCH(A2;{0;121;141;161;181;201;221;251});1;2;3;4;5;6;12;40)*$A$1
    4. fill formula down
    Warning : change argument separator if needed
    Remarks
    MATCH serching value greather or equal A2 and retuns it position in array {..}
    CHOSE returns value on indicate positiom
    Nemo

  5. #5
    New Member
    Join Date
    Feb 2002
    Location
    State? Insanity! :)
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok.. perhaps I am a moron, but I could not get either of those suggestions to work.
    I cut and pasted them directly from these posts.

    Aladin, your suggestion simply game me #name? error .. so I am thinking I am not schooled in the nature of "naming" cells?

    As I am now I have 1 document with 4 worksheets, one is named "admin" and cell a1 I entered in the value of X, and "named" it Price but it doesnt show as that name again when I check the cell, should it ?

    Help .. I'm at a loss! thanks!



    [ This Message was edited by: vampcatt on 2002-03-16 18:31 ]

  6. #6
    New Member
    Join Date
    Feb 2002
    Location
    State? Insanity! :)
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Anyone have any ideas?

    thanks!

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,809
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    On 2002-03-16 18:28, vampcatt wrote:
    Ok.. perhaps I am a moron, but I could not get either of those suggestions to work.
    I cut and pasted them directly from these posts.

    Aladin, your suggestion simply game me #name? error .. so I am thinking I am not schooled in the nature of "naming" cells?

    As I am now I have 1 document with 4 worksheets, one is named "admin" and cell a1 I entered in the value of X, and "named" it Price but it doesnt show as that name again when I check the cell, should it ?

    Help .. I'm at a loss! thanks!

    [ This Message was edited by: vampcatt on 2002-03-16 18:31 ]
    You get the #NAME? error because the naming action did not succeed. Giving a name to a single or multi-cell range is not difficult. You probably did not hit enter after typing Price in the Name Box. Try again.

    I've sent you a WB that shows what I suggested.

    Aladin

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com