If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Complex IF statement help
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Complex IF statement help

#1
August 28th 12, 02:02 AM
 pjcorc502 Junior Member First recorded activity by ExcelBanter: Aug 2012 Posts: 1
Complex IF statement help

I need help with this formula. This works to find out if certain pre-tax deductions on a pay-stub are included in a cafe plan. F4 would be the gross pay check amount. M10 is the total of all entered pre-tax deductions. The formula does the computation and then matches the result against F10 (the FICA tax amount entered from the pay-stub) which should equal each other if the correct deductions are entered. Here's the problem, I need to modify this to also come up with a true statement (You found the cafe plan) not only when the result equals F10, but also when it is within .02 cents either way of F10. Is this possible? Please help!

=IF(ROUND((F4-M10)*0.0565,2)=F10,"You found the café plan!","Not the café plan")

What i have entered works great when the gross tax amount adjusted for pre-tax deductions equals the FICA tax as it should, I just need there to be some degree of variance allowed for F10. Two cents either way should do it.
#2
September 13th 12, 03:56 PM
 skirkpatrick Junior Member First recorded activity by ExcelBanter: Sep 2012 Posts: 2

Quote:
 Originally Posted by pjcorc502 I need help with this formula. This works to find out if certain pre-tax deductions on a pay-stub are included in a cafe plan. F4 would be the gross pay check amount. M10 is the total of all entered pre-tax deductions. The formula does the computation and then matches the result against F10 (the FICA tax amount entered from the pay-stub) which should equal each other if the correct deductions are entered. Here's the problem, I need to modify this to also come up with a true statement (You found the cafe plan) not only when the result equals F10, but also when it is within .02 cents either way of F10. Is this possible? Please help! =IF(ROUND((F4-M10)*0.0565,2)=F10,"You found the café plan!","Not the café plan") What i have entered works great when the gross tax amount adjusted for pre-tax deductions equals the FICA tax as it should, I just need there to be some degree of variance allowed for F10. Two cents either way should do it.
You can do this by combining the AND Function with your IF Function and greater than / less than conditions for the 2 cents.

=IF(AND((ROUND((F4-M10)*0.0565,2)+0.02>=F10),((ROUND((F4-M10)*0.0565,2)-0.02<=F10))),"You found the café plan!","Not the café plan")

Last edited by skirkpatrick : September 13th 12 at 04:03 PM. Reason: Incorrect answer for all conditions - amended!
#3
October 18th 12, 05:14 PM
 AshKhan Junior Member First recorded activity by ExcelBanter: Oct 2012 Posts: 5

Quote:
 Originally Posted by skirkpatrick You can do this by combining the AND Function with your IF Function and greater than / less than conditions for the 2 cents. =IF(AND((ROUND((F4-M10)*0.0565,2)+0.02>=F10),((ROUND((F4-M10)*0.0565,2)-0.02<=F10))),"You found the café plan!","Not the café plan")
Hi

would I need to do an If statement is if want to do the following ?

i need help with something that looks simple but i cant for the life of me work it out

In cells E10 i want to input a date e.g 01/02/12
i want the worksheet to automatically work out
what target i have hit in cell G10

my targets are
13 weeks
26 week
39 week
52 week
52+

i want the cell G10 to populate the content into one of the 5 targets based on the date i put in

so if i input

01/02/12 cell G10 should automatically have text entered "39 Week"

i also want the formula to work in all cells E10 and G10 onwards

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post Complex IF OR AND Statement Karen53 Excel Programming 8 October 30th 10 04:38 PM complex if statement kdel1201 Excel Worksheet Functions 1 November 8th 09 03:02 PM Complex IF Statement Jonni Excel Discussion (Misc queries) 7 June 26th 09 04:09 PM Complex IF THEN statement Greg S Excel Worksheet Functions 5 December 8th 08 11:40 PM complex IF statement cathy Excel Programming 5 September 21st 07 08:34 PM

All times are GMT +1. The time now is 01:51 AM.