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.
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.