Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Help creating a cost range
I need help creating a cost range for the following scenario.
If the value of cell A1 falls between 0-300 I need the following cell A2 to say $0-$300 or if A1 was 389 I would need A2 to say $300-$600 and so on. In need it broke down in $300 increments to $6000. Thank You |
#2
|
|||
|
|||
Try a VLOOKUP. See:
http://www.officearticles.com/excel/...soft_excel.htm ************ Anne Troy www.OfficeArticles.com "tysonstone" wrote in message ... I need help creating a cost range for the following scenario. If the value of cell A1 falls between 0-300 I need the following cell A2 to say $0-$300 or if A1 was 389 I would need A2 to say $300-$600 and so on. In need it broke down in $300 increments to $6000. Thank You |
#3
|
|||
|
|||
="$"&A1-MOD(A1,300)&"-$"&300+A1-MOD(A1,300)
This formula should work for you. This puts numbers in the upper portion of their range (ie. 0 in A1 results in $0-$300 in A2, and 3600 in A1 results in $3600-$3900 in A2. "tysonstone" wrote: I need help creating a cost range for the following scenario. If the value of cell A1 falls between 0-300 I need the following cell A2 to say $0-$300 or if A1 was 389 I would need A2 to say $300-$600 and so on. In need it broke down in $300 increments to $6000. Thank You |
#4
|
|||
|
|||
Use a lookup table.
In cell E1 enter 0, in E2 enter 301, in E3 enter =E2+300 copy E3 down through E4:E21 In cell F1 enter ="$"&E1&" to $"&E2-1 Copy down through cells F2:F20 In cell F21 enter "Over $6000" In cell A2 enter =VLOOKUP(A1,$E$1:$F$21,2) Move location of table to suit and change ranges in formula. Regards Roger Govier tysonstone wrote: I need help creating a cost range for the following scenario. If the value of cell A1 falls between 0-300 I need the following cell A2 to say $0-$300 or if A1 was 389 I would need A2 to say $300-$600 and so on. In need it broke down in $300 increments to $6000. Thank You |
#5
|
|||
|
|||
Hi
Very neat solution! Well done. Regards Roger Govier Sloth wrote: ="$"&A1-MOD(A1,300)&"-$"&300+A1-MOD(A1,300) This formula should work for you. This puts numbers in the upper portion of their range (ie. 0 in A1 results in $0-$300 in A2, and 3600 in A1 results in $3600-$3900 in A2. "tysonstone" wrote: I need help creating a cost range for the following scenario. If the value of cell A1 falls between 0-300 I need the following cell A2 to say $0-$300 or if A1 was 389 I would need A2 to say $300-$600 and so on. In need it broke down in $300 increments to $6000. Thank You |
#6
|
|||
|
|||
THIS IS GREAT,
THANK YOU. "Sloth" wrote: ="$"&A1-MOD(A1,300)&"-$"&300+A1-MOD(A1,300) This formula should work for you. This puts numbers in the upper portion of their range (ie. 0 in A1 results in $0-$300 in A2, and 3600 in A1 results in $3600-$3900 in A2. "tysonstone" wrote: I need help creating a cost range for the following scenario. If the value of cell A1 falls between 0-300 I need the following cell A2 to say $0-$300 or if A1 was 389 I would need A2 to say $300-$600 and so on. In need it broke down in $300 increments to $6000. Thank You |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I use named range in data range box when creating pie chart? | Charts and Charting in Excel | |||
Question regarding dynamic range setting | Excel Worksheet Functions | |||
Indexing a row | Excel Worksheet Functions | |||
our cost to customers cost | Excel Worksheet Functions | |||
Cannot Expand Named Range - when size of the Range exceeds | Excel Discussion (Misc queries) |