excel - Formula to find value between date ranges and then use the figure next to it -


i have developed worksheet office work. have developed formula used in it. however, formula quite long , want shortened.

here detail formula.

=if(   and(     [date]>=date(2014,7,1),     [date]<=date(2014,7,31),     [description]="diesel oil"),   [value ex. sales tax]*'tax status'!$b$3,   if(     and(       [date]>=date(2014,8,1),       [date]<=date(2014,8,31),       [description]="diesel oil"),     [value ex. sales tax]*'tax status'!$b$4,   if(     and(       [date]>=date(2014,9,1),       [date]<=date(2014,9,30),       [description]="diesel oil"),     [value ex. sales tax]*'tax status'!$b$5,   if(     and(       [date]>=date(2014,10,1),       [date]<=date(2014,10,31),       [description]="diesel oil"),     [value ex. sales tax]*'tax status'!$b$6,   if(     and(       [date]>=date(2014,11,1),       [date]<=date(2014,11,30),       [description]="diesel oil"),     [value ex. sales tax]*'tax status'!$b$7,   if(     and(       [date]>=date(2014,12,1),       [date]<=date(2014,12,31),       [description]="diesel oil"),     [value ex. sales tax]*'tax status'!$b$8,   if(     and(       [date]>=date(2015,1,1),       [date]<=date(2015,1,31),       [description]="diesel oil"),     [value ex. sales tax]*'tax status'!$b$9,   if(     and(       [date]>=date(2015,2,1),       [date]<=date(2015,2,28),       [description]="diesel oil"),     [value ex. sales tax]*'tax status'!$b$10,   if(     and(       [date]>=date(2015,3,1),       [date]<=date(2015,3,31),       [description]="diesel oil"),     [value ex. sales tax]*'tax status'!$b$11,   if(     and(       [date]>=date(2015,4,1),       [date]<=date(2015,4,30),       [description]="diesel oil"),     [value ex. sales tax]*'tax status'!$b$12,   if(     and(       [date]>=date(2015,5,1),       [date]<=date(2015,5,31),       [description]="diesel oil"),     [value ex. sales tax]*'tax status'!$b$13,   if(     and(       [date]>=date(2015,6,1),       [date]<=date(2015,6,30),       [description]="diesel oil"),     [value ex. sales tax]*'tax status'!$b$14,   if(     and([description]="furnace oil"),     [value ex. sales tax]*0.17,if(and([description]="lubricants"),[value ex. sales tax]*0.17,0)   )))))))))))))

this formula entered in main table under header "sales tax".

this other table taking tax rates.

month   tax rate jul-14  17% aug-14  17% sep-14  17% oct-14  17% nov-14  17% dec-14  17% jan-15  22% feb-15  27% mar-15  37% apr-15  32% may-15  34% jun-15

if there unclear, please let me know make clearer.

assuming date formatted , not stored text, should work:

=if(     [description] = "diesel oil",     index(         taxstatus!$b:$b,         match(             date(                 year(                     [date]                 ),                 month(                     [date]                 ),                 1             ),             taxstatus!$a:$a,             0         )     ) * [valueex.salestax],     if(         or(             [description] = "furnace oil",             [description] = "lubricants"         ),         [valueex.salestax] * 0.17,         0     ) )

(edited add final 2 criteria end , fix couple of errors.)


Comments

Post a Comment

Popular posts from this blog

Magento/PHP - Get phones on all members in a customer group -

i working on custom magento extension. working around adminhtml part , i've created custom form there. here form code: <?php class vivasindustries_smsnotification_block_adminhtml_sms_sendmass_edit_form extends mage_adminhtml_block_widget_form { public function _preparelayout() { $extensionpath = mage::getmoduledir('js', 'vivasindustries_smsnotification'); $head = $this->getlayout()->getblock('head'); $head->addjs('jquery.js'); $head->addjs('vivas.js'); return parent::_preparelayout(); } protected function _prepareform() { $form = new varien_data_form(array( 'id' => 'edit_form', 'action' => $this->geturl('*/*/save', array('id' => $this->getrequest()->get...
Read more

php - Bypass Geo Redirect for specific directories -

i'm trying bypass redirect specific url within site. wondering if might able help. the code below code use, original urls replaces example.com i'd able access http://example.com/admin without redirect occuring. is there way this, , there way using own ip? function country_geo_redirect() { $country = getenv('http_geoip_country_code'); if ($country == "us" ) { wp_redirect("http://usa.example.com/"); exit; } else if ($country == "gb" ) { wp_redirect("http://uk.example.com/"); exit; } } add_action('init', 'country_geo_redirect'); you can bypass redirection code if server found ip address. if ip address == <my ip address> ' nothing here else country_georedirect() end if
Read more

php - .htaccess mod_rewrite for dynamic url which has domain names -

i want convert url ex:( example.com/link.php?url=facebook.com ) ( example.com/facebook.com.php ) how can using .htaccess. or there other process make job easier ? i tried adding script .htaccess file options +followsymlinks rewriteengine on rewriterule url-(.*)\.htm$ link.php?url=$1 but converting url adding "url-" ( example.com/url-facebook.com.php ) i want remove "url-" , want make ( example.com/facebook.com.php ) rewriteengine on rewritecond %{the_request} ^get.*link\.php\?url\= [nc] rewritecond %{request_uri} !/system/.* [nc] rewriterule (.*?)link\.php\?url\=/*(.*) /$1$2 [r=301,ne,l] not sure how above work edit of used use remove index.php url
Read more