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

Author Message
Bfx
General User

Joined: 03 Mar 2012
Posts: 10

 Posted: Sat Mar 03, 2012 3:58 pm    Post subject: [Resolved] help calculating new price values based on ranges Hi, I'm looking to create a new column H that takes values from column G and calculates prices by doing the following: *In case the last part was unclear, I want all numbers to end in .99. Can anyone help me with this formula? Thanks Last edited by Bfx on Sat Mar 03, 2012 7:27 pm; edited 1 time in total
ozzie

Joined: 29 Jul 2010
Posts: 381
Location: victoria

Posted: Sat Mar 03, 2012 5:08 pm    Post subject:

perhaps

 Code: =IF(MOD(IF(G1<10;G1*1.5;IF(G1>100;G1*1.2;G1*1.3));1)=0;IF(G1<10;G1*1.5;IF(G1>100;G1*1.2;G1*1.3))+0.99;ROUNDUP(IF(G1<10;G1*1.5;IF(G1>100;G1*1.2;G1*1.3));0)-0.01)

1 - I have assumed everything over 100 is subject to 120% (which includes over 1000)
2 - Your percentage reference can be read two ways,eg (2*1.5) or (2+(2*1.5)), my formula uses the first option.(If the latter is required then 1.5 should be replaced with 2.5, 1.3 with 2.3 and 1.2 with 2.2.
_________________
ken johnson
Super User

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

Posted: Sat Mar 03, 2012 6:06 pm    Post subject:

Try...
 Code: =IF(ISNUMBER(G2);TRUNC(G2*VLOOKUP(G2;{0;1.5|10;1.3|100;1.2|1000;1};2))+0.99;"")
in H2 filled down column H, or, alternatively the lookup array {0;1.5|10;1.3|100;1.2|1000;1} could be in 8 cells on the sheet.
Say J2:K5 has these values...
 Code: J       K 2     0     1.5 3    10     1.3 4   100     1.2 5  1000     1.0
then the formula would be...
 Code: =IF(ISNUMBER(G2);TRUNC(G2*VLOOKUP(G2;\$J\$2:\$K\$5;2))+0.99;"")

I appear to have made the same assumptions as ozzie because ozzie's formula produces the same results.
EDIT: Except for the assumption regarding Prices >= \$1000, where I have assumed 100% (1.0)

Ken Johnson
_________________
Bfx
General User

Joined: 03 Mar 2012
Posts: 10

 Posted: Sat Mar 03, 2012 7:26 pm    Post subject: Excellent guys, it works perfectly; thanks so much!
ozzie

Joined: 29 Jul 2010
Posts: 381
Location: victoria

 Posted: Sun Mar 04, 2012 4:20 pm    Post subject: Ken Very clean neat and accurate I love watching you work_________________If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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