Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-25-2017, 05:36 PM
riothecat riothecat is offline multiple if functions in 1 cell Windows 10 multiple if functions in 1 cell Office 2007
Novice
multiple if functions in 1 cell
 
Join Date: Nov 2016
Posts: 27
riothecat is on a distinguished road
Default multiple if functions in 1 cell

the following is what I need but cannot get close to what I need to do. Any help is appreciated...

In cell a2 IF A1 >0 BUT < 96 THEN A3
OR IF A1 > 96.01 but< 192 THEN A4


OR IF A1>192 but < 288THEN A5
OR IF A1>288 but < 384 THEN A6
OR IF A1>384 but <769 THEN A7
basically it is a tax withholding formula for my business.
to appear in a2


Thank In Advance
Reply With Quote
  #2  
Old 05-25-2017, 11:34 PM
xor xor is offline multiple if functions in 1 cell Windows 10 multiple if functions in 1 cell Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Maybe:

=IF((A1>384)*(A1<769),A7,IF(A1>288,A6,IF(A1>192,A5 ,IF(A1>96,A4,IF(A1>0,A3)))))

If this is not what you want, please give a detailed example.
Reply With Quote
  #3  
Old 05-26-2017, 07:00 AM
riothecat riothecat is offline multiple if functions in 1 cell Windows 10 multiple if functions in 1 cell Office 2007
Novice
multiple if functions in 1 cell
 
Join Date: Nov 2016
Posts: 27
riothecat is on a distinguished road
Default

MORE THAN NOT MORE THAN WITHOLD OVER
$0.00 $96.15 $0.00 PLUS 0.556%
$96.16 $192.30 $0.53 PLUS 1.112% $96.15
$192.31 $288.45 $1.60 PLUS 2.226% $192.30
$288.46 $384.60 $3.74 PLUS 2.782% $288.45
$384.61 $769.20 $6.41 PLUS 3.338% $384.60
$769.20 $1,538.40 $19.25 PLUS 3.894% $769.20

$256 <<<that is the pay so I would deduct $192.30 from the 256 and take 2.782%and $1.60 from that remainder for a total tax of $3.02


=IF((A1>384)*(A1<769),A2,IF(A1>288,A3,IF(A1>192,A4 ,IF(A1>96,A5,IF(A1>0,A6)))))

this FORMULA (THE ONE FROM XOR) sort of works, but not quite. I need it to reference cells, that I will put in the actual formulas, n=but if you can make the above table work that would be great.

Last edited by riothecat; 05-26-2017 at 07:06 AM. Reason: clean up chart
Reply With Quote
  #4  
Old 05-26-2017, 08:33 AM
xor xor is offline multiple if functions in 1 cell Windows 10 multiple if functions in 1 cell Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Is it like shown in the attached?
Attached Files
File Type: xlsx Tax.xlsx (14.8 KB, 8 views)
Reply With Quote
  #5  
Old 05-26-2017, 08:58 AM
riothecat riothecat is offline multiple if functions in 1 cell Windows 10 multiple if functions in 1 cell Office 2007
Novice
multiple if functions in 1 cell
 
Join Date: Nov 2016
Posts: 27
riothecat is on a distinguished road
Default

That works very well. The only problem with (me) and vlookup is, I have many employees and I do payroll in a different worksheet inside one work book. and I am using a vlookup for the tax table now and had it set for 2 years but now have to extend it for another 2 years and I Cannot figure out how to copy the previous good vlookup cell to the new and have it change the cell I need to change and have the vlookup box stay the same. So I copy down 104 weeks and then have to go to each week and change the vlookup so it is right and have to do this with 13 employees and one employee took me 2.5 hours last nigh. that's Why I was hoping to do a multiple if statement. I can do 1 if statement but need 5 in the same line and don't know what separator to use. Sorry for the length but its the best way I can fully explain.
Reply With Quote
  #6  
Old 05-26-2017, 10:08 AM
xor xor is offline multiple if functions in 1 cell Windows 10 multiple if functions in 1 cell Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

Is it better now?
Attached Files
File Type: xlsx Tax_2.xlsx (15.0 KB, 9 views)
Reply With Quote
  #7  
Old 05-26-2017, 10:51 AM
riothecat riothecat is offline multiple if functions in 1 cell Windows 10 multiple if functions in 1 cell Office 2007
Novice
multiple if functions in 1 cell
 
Join Date: Nov 2016
Posts: 27
riothecat is on a distinguished road
Default

Perfect. Thank you for saving me hours
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
multiple if functions in 1 cell Counting multiple data in cell more than once Alaska1 Excel 3 03-01-2015 04:27 PM
multiple if functions in 1 cell Help with creating an invoice calculator with multiple drop down menus and functions excerbated123 Excel 10 02-03-2015 03:07 PM
multiple if functions in 1 cell I need to add multiple values based on multiple criteria in a cell not sure what to do AUHAMM Excel 3 10-27-2014 09:11 PM
Multiple data in one cell? New Daddy Excel 4 09-06-2013 02:50 AM
Splitting Excel cell into multiple ruben23 Excel 1 07-23-2009 03:49 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:03 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft