• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help in creating IF/AND formula

Heavynbound

New Member
I am trying to create an IF/AND formula based on multiple factors but I am getting confused and cannot get the formula to do everything I need it to do. I have only been using the IF/AND formula for 4 months so I am pretty new at what all it can do and have a lot to learn. I need an IF/ANDF formula that states if the date in one cell is greater than the date in another cell and the contents of a third cell is equal to an account number, then use the overhead (OH) rates for FY 2012, otherwise use the overhead (OH) rates for FY 2012.


A1 = December 2011 Account # FY 2011 Rate FY 2012 Rate

1000-000 29.21% 15.54%

1000-100 44.86% 31.34%

1000-600 24.35% 20.21%

Example:

If if D3 (April 2012) is greater than A1 (December 2011) and account number equal 1000-000, then use rate 15.54% or

If D3 (April 2012) is greater than A1 (December 2011) and account number equal 1000-100, then use rate 31.34% or

If D3 (April 2012) is greater than A1 (December 2011) and account number equal 1000-600, then use rate 20.21% or

If D3 (April 2012) is less than A1 (December 2012) and account number equal 1000-000, then use rate 29.21% or

If D3 (April 2012) is less than A1 (December 2012) and account number equal 1000-100, then use rate 44.86% or

If D3 (April 2012) is less than A1 (December 2012) and account number equal 1000-600, then use rate 24.35%


Is there a nested IF/AND or IF/OR formula or any formula that will work? My boss is asking for this spreadsheet and I have everything else completed except this piece which calculate the employees applicable overhead rate based on if a particular month is greater than or less than the month in a cell and a particular account code


Thanks in advance.
 
Hi, Heavynbound!


If you have read the first three (or four) green sticky posts at this forums main page...

http://chandoo.org/forums/topic/phd-forum-posting-rules-etiquette-pls-read-before-posting

...you should have noticed this points:


"Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out."


"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."


"Cross Posting, generally it is considered poor practice to cross post, that is to post the same question on several forums in the hope of getting a response quicker."


"If you do cross post, please put that in your post."


"Also if you have cross posted and get an answer elsewhere, have the courtesy of posting the answer here so other readers can learn from the answer also, as well as stopping people wasting there time on your answered question."


"If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."


"Say "Thanks", whenever you can. Recognize when someone has bothered to go to the trouble and time to assist you with your question. Often readers will spend several hours working on a solution to a problem, a line of recognition will go a long way."


Regards!


PS: I edited your topic title. Changed from uppercase too so as to not seem to look like a shout.
 
Hi ,


Using nested IFs will make the statement a very long one ; can you use VLOOKUP ?


Suppose your data on the rates is in the range O11:Q13 as follows :

[pre]
Code:
1000-000	29.21%	15.54%
1000-100	44.86%	31.34%
1000-600	24.35%	20.21%
[/pre]
Suppose we call this range Rate_Table.


Suppose we call the cell where the account number is entered Account_Number.


The following formula will give you what you want :


=VLOOKUP(Account_Number,Rate_Table,IF(D3>A1,3,IF(D3<A1,2,1)),FALSE)


I have put a dummy value of 1 for the condition when D3 = A1 , since you have not specified what should happen in this case. You can change it to one of the following :


=VLOOKUP(Account_Number,Rate_Table,IF(D3>A1,3,2),FALSE)


=VLOOKUP(Account_Number,Rate_Table,IF(D3>=A1,3,2),FALSE)


Narayan
 
Hi, Heavynbound!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Need%20help%20in%20creating%20IF_AND%20formula%20%28for%20Heavynbound%20at%20chandoo.org%29.xlsx


Yellow shaded cells A1-D3-B5 are the input parameters; table at F1:H4 cells is the rate definition; B7 formula is what you asked for (formula IF/AND), and B6 is a solution with no IF/AND.


Just advise if any issue.


Regards!
 
@NARAYANK991

Hi!

Didn't update topic since one of my favorite series began, I overstepped you, sorry.

I arrived to a similar but shorter formula:

=BUSCARV(B5;$F$2:$H$4;(D3>A1)+2;FALSO) -----> in english: =VLOOKUP(B5,$F$2:$H$4,(D3>A1)+2,FALSE)

Regards!
 
Back
Top