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

Rooksboy1979

New Member
Joined
Aug 21, 2014
Messages
9
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Rooksboy1979,

Welcome to MrExcel.

Maybe try....

Excel 2007
KLMNOPQ
1183n/a100
Sheet9
Cell Formulas
RangeFormula
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.
 
Upvote 0
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
 
Upvote 0
Apologies. 2003 / 2007 is a s plain as day in the title. :oops:

Aladin has it nicely sorted!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top