[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
bitmap
Newbie

Joined: 04 Jul 2011
Posts: 4

 Posted: Mon Jul 16, 2012 7:46 am    Post subject: How to convert numbers in set range Hi, I've looked everywhere but i can't seem to find ether the formula or method to do: Convert a numerical value ex, 54,2 in to a different number with it's value being dictated by a range for example: Range = equal 0-9,5 = 20 9,5-20 = 30 20-30 = 43 With a simple calculating formula generating number A and formula B converting that in to the value as set by the chart. Can anyone help me out?
scsisys

Joined: 17 Dec 2009
Posts: 244

 Posted: Mon Jul 16, 2012 10:50 am    Post subject: bitmap... If you are wanting : values LESS THAN 9.5 to be 20 ; values GREATER THAN OR EQUAL TO 20 to be 43 ; values GREATER THAN OR EQUAL TO 9.5 AND values LESS THAN 20 to be 30 , try the following: =IF(A1="";"";IF(A1<9.5;20;IF(A1>=20;43;30))) If you are wanting : values LESS THAN OR EQUAL TO 9.5 to be 20 ; values GREATER THAN 20 to be 43 ; values GREATER THAN 9.5 AND values LESS THAN OR EQUAL TO 20 to be 30 , try the following: =IF(\$A1="";"";IF(\$A1<=9.5;20;IF(\$A1>20;43;30))) scsisys_________________OO 3.2.1 Win XP /SP3
bitmap
Newbie

Joined: 04 Jul 2011
Posts: 4

 Posted: Tue Jul 17, 2012 3:30 am    Post subject: Thanks! Although when i see the formula it's going to be to long I need roughly 100 variables. What i'm trying to do is match a package dimensional weight with a shipping price chart. that works in increments of 0.5KG (0-50KG!) So it can auto calculate freight costs.
ken johnson
Super User

Joined: 23 Apr 2009
Posts: 2027
Location: Sydney, Australia

Posted: Tue Jul 17, 2012 4:20 am    Post subject:

Say A1:A3 has these cut-off weights...
0
9,5
20
and B1:B3 has these freight costs...
20
30
43
and C1 has any weight greater than 0, then
 Code: =LOOKUP(C1;\$A\$1:\$A\$3;\$B\$1:\$B\$3)
returns the appropriate freight cost.

Ken Johnson
_________________