Forum Discussion
Solved
When using IF formula how to return blank cell if error
Hi all,
Thanks for the help in advance. I have 2 issues which I'm having trouble with.
1. I'm currently trying to create an excel document which will subtract one cell from another however when there is an error, it returns "#VALUE!". Is there a way to return a blank cell with no text if an error occurs. I have tried using "" and "IFERROR" but I cannot seem to incorporate them into the same cell. The formula I'm using at the moment is as follows: =IF(A12=6,E11-B12,"") however on the odd occasion there may be a piece of text or 2 numbers in the cell B12 which causes the error.
2. This is causing an error on the next sheet which brings me to my 2nd issue. I am trying to display the amended cells onto the next sheet in order to export the document with the amended numbers. Due to the error on the first sheet, "#VALUE!" is being displayed on the second one and the original unamended numbers are not being shown. I assume if there 1st issue was resolved then the 2nd issue would resolve itself.
The need for this is to change our 2D levels to 3D and then export the excel document in to a separate piece of software. I have attached screenshots of the pages and formulas being used.
Let me know if you have any questions. Thankyou.
=IFERROR(IF(A12=6,E11-B12,""),"")This is the formula in sheet 1 which returns an empty cell in case of an error in my worksheet.
=IF(ISERROR(IF('Kyne Loadfile Test 1'!A12=6,'Kyne Loadfile Test 1'!E11-'Kyne Loadfile Test 1'!B12,"")),'Kyne Loadfile Test 1'!E11,'Kyne Loadfile Test 1'!H11)This formula returns the value from cell E11 of sheet 1 if there is an error in the calculation E11-B12.
Kyne Loadfile Test 1:
Kyne Loadfile Test 2:
.
=IFERROR(IF(A12=6,E11-B12,""),"")This is the formula in sheet 1 which returns an empty cell in case of an error in my worksheet.
=IF(ISERROR(IF('Kyne Loadfile Test 1'!A12=6,'Kyne Loadfile Test 1'!E11-'Kyne Loadfile Test 1'!B12,"")),'Kyne Loadfile Test 1'!E11,'Kyne Loadfile Test 1'!H11)This formula returns the value from cell E11 of sheet 1 if there is an error in the calculation E11-B12.
Kyne Loadfile Test 1:
Kyne Loadfile Test 2:
.
Firstly, thankyou for the response. This has solved the first issue however it hasn't resolved the second one. This has displayed all the original levels which were subject to error previously however it is not showing any of the previous levels which did not need any subtraction from them. Would it be possible to incorporate this into the formula as every time we were to use this excel document, the layout of which levels would need subtraction would be different.
I have shown the formula used on both sheet 2 and sheet 1. Thankyou in advance.
You are welcome. I'm not sure what the expected results are. I've set up an Excel file with parts of your data. Can you share the expected results in cells E11, E12 and so on?
Sheet "Kyne Loadfile Test 2":
