OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

How to convert numbers in set range

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
bitmap
Newbie
Newbie


Joined: 04 Jul 2011
Posts: 4

PostPosted: Mon Jul 16, 2012 7:46 am    Post subject: How to convert numbers in set range Reply with quote

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?
Back to top
View user's profile Send private message
scsisys
OOo Advocate
OOo Advocate


Joined: 17 Dec 2009
Posts: 248

PostPosted: Mon Jul 16, 2012 10:50 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
bitmap
Newbie
Newbie


Joined: 04 Jul 2011
Posts: 4

PostPosted: Tue Jul 17, 2012 3:30 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


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

PostPosted: Tue Jul 17, 2012 4:20 am    Post subject: Reply with quote

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
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group