excel - Errors when nesting IF() formulas -


i'm putting simple spreadsheet calculates tax owed based on few different bands. have 'invoiced amount' cell takes yearly invoicing , applies tax rate based on following conditions:

if 'invoiced amount' below 10, 600 tax owed = 0

if 'invoiced amount' above 10, 600 less 42, 386 tax owed = ((invoiced amount - tax allowance)/100) * 20

if 'invoiced amount' equal or greater 42, 386 tax owed = ((42, 386 - tax allowance)*20)+((invoicedamount - 42, 386)*40)

i overlooking basic here, sure - tax allowance 10, 600 - on 42, 386 worked out @ 20% tax, , earn above 42, 386 charged @ 40% on top...

the more type out more confused am. anyway, here excel formula:

invoicedtotal = p5  taxallowance (10600) ='uk tax figures'!c3  taxallowanceupperband (42386) ='uk tax figures'!g5  taxallowance upper band - tax allowance (31784) = h5  uppertaxallownace band (42386.01) = ='uk tax figures'!f5   =if ((p5)<’uk tax figures’!c3, 0, if(p5>=’uk tax figures’!c3<'uk tax figures'!g5, ((p5-'uk tax figures'!c3)/100)*20, if(p5>='uk tax figures'!f5, ((h5/100)*20)+((p5-'uk tax figures'!f5)*40))

at moment i'm getting crazy unexpected values back, calculation wrong... can't see wood trees @ moment, if has thoughts appreciate it! going little crazy here @ moment!

putting formula online excel formula beautifier notice several problems formula:

=if ( ( p5 ) < ’uk tax figures’!c3 , 0 ,     if(         p5 >= ’uk tax figures’!c3 < uktaxfigures!g5,         ( ( p5 - uktaxfigures!c3 ) / 100 ) * 20,         if(             p5 >= uktaxfigures!f5,             ( ( h5 / 100 ) * 20 ) + ( ( p5 - uktaxfigures!f5 ) * 40 )         )
  • first of all, lack 2 closing parantheses
  • furthermore, have invalid conditional in p5>=’uk tax figures’!c3<'uk tax figures'!g5, need change and(p5>=uktaxfigures!c3;uktaxfigures!c3<uktaxfigures!g5).
  • taking @ how reference worksheets above, notice seems have 2 different sheets - 1 spaces between words in sheet names, , 1 without. suspect not case, should remove spaces formula.
  • you inconsistent whether or not use apostrophes (’) around sheet-names. trying out formula in cell, excel didn't seem them, should go well.
  • there seems return-value missing innermost if-statement if false.

guessing bit @ want formula return, end formula:

=if(p5<uktaxfigures!c3;0;if(and(p5>=uktaxfigures!c3;uktaxfigures!c3<uktaxfigures!g5);((p5-uktaxfigures!c3)/100)*20;if(p5>=uktaxfigures!f5;((h5/100)*20)+((p5-uktaxfigures!f5)*40);0)))

which looks in beautifier:

=if(     p5 < uktaxfigures!c3;     0;     if(         and(             p5 >= uktaxfigures!c3;             uktaxfigures!c3 < uktaxfigures!g5         );         ( ( p5 - uktaxfigures!c3 ) / 100 ) * 20;         if(             p5 >= uktaxfigures!f5;             ( ( h5 / 100 ) * 20 ) + ( ( p5 - uktaxfigures!f5 ) * 40 );             0         )     ) )

is close want?

as final word of advice absolute pain write formula - instead can try build bit bit, storing each part of formula in 1 cell see if each of them works. can cut , paste fit in 1 cell. i.e. if in cell a1 store:

=if(p5>=uktaxfigures!f5;((h5/100)*20)+((p5-uktaxfigures!f5)*40);0)

then can put 

=if(and(p5>=uktaxfigures!c3;uktaxfigures!c3<uktaxfigures!g5);((p5-uktaxfigures!c3)/100)*20;a1)

in a2 , this

=if(p5<uktaxfigures!c3;0;a2)

in a3. if necessary can pick formulas further apart make them more readable. in end copy formulas cells, , replace references them.

please note not 100 % got correct, without proper data difficult keep track of parantheses , results. should @ least give starting point.


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