Formula Help!!!!

G

Guest

Guest
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. <grin>
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.. :)
 
Upvote 0
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. <grin>

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
 
Upvote 0
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. <grin>
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top