IF AND ISERROR - Excel 2003 & Excel 2007 - Help needed to add in another function

[Rooksboy1979]

Hi all

This is my first post on here so please bear with me, but thank you for reading.

I would like to add an extra function into the formula below which was written by a work colleague who has since left the company. I have tried using my knowledge to amend it, but I am a novice compared to them and I cannot get it to work.

=IF(AND(N118="N/A",K118="N/A"),0,IF(AND(N118="N/A",K118<>"N/A"),K118,IF(AND(N118<>"N/A",K118="N/A"),N118,IF(ISERROR((K118-N118)),0,(K118-N118))))

When there are numbers in column N and K it calculates the variance between the two numbers correctly and displays the result in Column Q where the formula is located, also when there is an N/a written in both column N and K this shows correctly as a 0 in Column Q.

However what I would like it to also do in addition to the above function is when Column N has N/a and Column K has any number between 0 - 100 that it shows in column Q where the formula is located as 100. I would also like this to do the same in reverse if there was an N/a in Column K and a number between 0 - 100 in Column N. This part of the function is to show that an N/a vs any number is 100% different (i.e 100% out of variance)

I hope this all makes sense and any help you can give would be greatly appreciated.

Regards

 

[Snakehips]

Rooksboy1979,

Welcome to MrExcel.

Maybe try....

Excel 2007
	K	L	M	N	O	P	Q
118	3			n/a			100
Sheet9

Cell Formulas
Range	Formula
Q118	=IFERROR(IF(AND(K118="N/A",N118="N/A"),0,IF(AND(OR(K118="N/A",N118="N/A"),OR(AND(K118>0,K118<101),AND(N118>0,N118<101))),100,K118-N118)),0)

Hope that helps.

 

[Rooksboy1979]

Hi Tony

Thank you so much for your quick reply and solution, it works perfectly in Excel 2007. I only had to make a small alteration so that it would show N/a vs 0 as 100% different as well:

=IFERROR(IF(AND(K118="N/A",N118="N/A"),0,IF(AND(OR(K118="N/A",N118="N/A"),OR(AND(K118>-1,K118<101),AND(N118>-1,N118<101))),100,K118-N118)),0)

One question, is there any way to make this formula compatible with MS Excel 2003 or is it too complex? I sometimes have to access this spreadsheet from other areas of the office that have not been upgraded to a newer version of excel.

Again thank you for your help and for sharing your knowledge.

Regards

Ian

 

[Aladin Akyurek]

Try...

=CHOOSE(COUNT(K118,N118)+1,0,100,K118-N118)

 

[Snakehips]

Apologies. 2003 / 2007 is a s plain as day in the title.

Aladin has it nicely sorted!

 

[Rooksboy1979]

Hi Aladin/Tony

Thank you both for replying.. This is working a treat now in 2003.

This forum is now officially my favourite website, I hope I can one day be skilled enough to assist someone.

Thanks again.

Ian

 

[Aladin Akyurek]

Hi Aladin/Tony

Thank you both for replying.. This is working a treat now in 2003.

This forum is now officially my favourite website, I hope I can one day be skilled enough to assist someone.

Thanks again.

Ian

You are welcome.