Adding another layer to my IF function onion

[carlosbourn]

Hi Guys

I have the following formula:

=IF(OR(A12="",B12="",C12="",I12=""),"",IF(J12+0<=7,INDEX(Ports!$A$3:$BD$74,MATCH(Feb11!C12,PORT,0),MATCH(Feb11!F12,Ports!$A$2:$BD$2,1)),INDEX(Ports!$A$3:$BD$74,MATCH(Feb11!C12,PORT,0),MATCH(Feb11!F12,Ports!$A$2:$BD$2,1))/7*J12))

However, I now need to factor in another differential that can influence the result.

In a nutshell this looks for a specific set %age in a range based on a date provided. This date is one of two dates, the sum of which is shown in cell J12 - if the number of days between these dates exceeds 7 days, the result is divided by 7 and multiplied by the actual number of days in J12.

What I need to do now is add another function that changes the 'number of days' part of the calculation if there is a particular value in another cell.

For example, using the above formula as a guide:

If, say "ABC" is the value in cell C12, the resultant %age is not to be divided by 7 and multiplied by the actual number of days in J12, it needs to be simply the resultant %age only.

Is this possible? Sorry I can't attach the workbook, it contains sensitive information.

Thanks

Carl

 

[Andrew Poulsom]

Maybe try:

=IF(OR(A12="",B12="",C12="",I12=""),"",IF(J12+0<=7,INDEX(Ports!$A$3:$BD$74,MATCH(Feb11!C12,PORT,0),MATCH(Feb11!F12,Ports!$A$2:$BD$2,1)),INDEX(Ports!$A$3:$BD$74,MATCH(Feb11!C12,PORT,0),MATCH(Feb11!F12,Ports!$A$2:$BD$2,1))/IF(C12="ABC",7*J12,1)))

 

[carlosbourn]

Hi Andrew - thanks for your reply.

I have tried this but it is dividing the result by the sum of 7*J12 whereas I need it to divide the rate by 7 first, then multiply that by J12.

For instance, if J12 = 8...

The rate is just being divided by 56, rather than by 7, then multiplied by 8. How can I step this calculation in such a way?

 

[Andrew Poulsom]

Oops:

=IF(OR(A12="",B12="",C12="",I12=""),"",IF(J12+0<=7,INDEX(Ports!$A$3:$BD$74,MATCH(Feb11!C12,PORT,0),MATCH(Feb11!F12,Ports!$A$2:$BD$2,1)),INDEX(Ports!$A$3:$BD$74,MATCH(Feb11!C12,PORT,0),MATCH(Feb11!F12,Ports!$A$2:$BD$2,1))/7*IF(C12="ABC",7,J12)))

 

[njimack]

Don't know if this helps, but I think your original formula can be simplified to:

=IF(COUNTIF(A12:B12,""),"",INDEX(Ports!$A$3:$BD$74,MATCH(Feb11!C12,PORT,0),MATCH(Feb11!F12,Ports!$A$2:$BD$2,1))/IF(J12+0<=7,7*J12,1))

 

[carlosbourn]

Thanks guys - all works perfectly now

However, something else has just arisen as a result...

I now need to add another function which overrides the result if cell C12="ABC" - the dividing by 7 part is fine, but it needs to divide a fixed value rather that the result of the formula. Does this makes sense?!

I need to maintain the whole formula because the "ABC" part does not apply to all entities.

For example, if C12="ABC", I need the result to be "0.1" (before the divide by 7 part). It C12 does not equal "ABC" then the formula result is the right one.

I hope I've explained that ok. Sorry!

Thanks again for your invaluable help.

 

[carlosbourn]

Having another go this morning trying to factor the above into this formula but I think I need a lie down. Argh.

=IF(OR(A12="",B12="",C12="",I12=""),"",IF(J12+0<=7,INDEX(Ports!$A$3:$BD$74,MATCH(Feb11!C12,PORT,0),MATCH(Feb11!F12,Ports!$A$2:$BD$2,1)),INDEX(Ports!$A$3:$BD$74,MATCH(Fe b11!C12,PORT,0),MATCH(Feb11!F12,Ports!$A$2:$BD$2,1))/7*IF(C12="ABC",7,J12)))

I now need to add another function which overrides the result if cell C12="ABC" - the dividing by 7 part is fine, but it needs to divide a fixed value rather that the result of the formula. Does this makes sense?!

I need to maintain the whole formula because the "ABC" part does not apply to all entities.

For example, if C12="ABC", I need the result to be "0.1" (before the divide by 7 part). It C12 does not equal "ABC" then the formula result is the right one.

 

[Andrew Poulsom]

Is it?

=IF(OR(A12="",B12="",C12="",I12=""),"",IF(C12="ABC",0.1,IF(J12+0<=7,INDEX(Ports!$A$3:$BD$74,MATCH(Feb11!C12,PORT,0),MATCH(Feb11!F12,Ports!$A$2:$BD$2,1)),INDEX(Ports!$A$3:$BD$74,MATCH(Feb11!C12,PORT,0),MATCH(Feb11!F12,Ports!$A$2:$BD$2,1))/7*IF(C12="ABC",7,J12))))