isblank to ignore formula

[Cidona]

Dear Forum.

Wondering if someone can give me some guidance on how to achieve the following? Please see attached file which is a theoritical sample of what I'm trying to accomplish.

There are two 'Examples' in the attached worksheet (example 1 and 2). They are basically the same thing except that in example 1 the 'Price' input cell (C5) is looking at another cell (i.e. contains a formula) for it's value (looks to cell K5). In example 2 the value is to be input by the user.
For example 1 the answer is returned in cell D15, which has the same formula structure as the answer cell (D32) in example 2.

So, example 2 is producing the effect that I want, i.e. unless each of the 5 inputs (cells C22, C24, C26, C28 and C30) have values then the answer (cell C32) returns "Missing Info".

I want to be able to do the equivalent for example 1, i.e. that unless there is inputs in cells C5, C7, C9, C11 and C13 that it would return the answer in Cell D15 as "Missing Info". However, because in example 1, cell C5 contains a formula, the answer (cell D15) returns "0" rather than "Missing Info".

Is there a way to overcome this so that cell D15 would return "Missing Info" rather than 0 prior to the input of all the cells?

Any insight would be appreciated.

[Fotis1991]

Hi Cidona

In D15, put this formula:

=IF(AND(C5=0;C7=0;C9=0;C11=0;C13=0);"Missing Info";(C5*C9*(C11/100)*C13)+(C7*C9*(C11/100)*C13))

Is it OK now??

Hope to helps you.

[adaws]

Please check the attached file. hope this helps

[TMS]

But, sadly, in the first example, the cell is NOT blank, it has a formula which is returning a zero value. Excel, ever helpful, will do that when the cell that is referred to is empty. If you put a space in cell K5, you'd get a space in cell C5 but that would screw up the formula in D15. Similarly, =IF(K5="","",K5) returns a null if K5 is empty but breaks your formula.

Note that your ISBLANK statement is ONLY testing cell C5, not C7, C9, C11 and C13.

Personally, I'd go with:

=IF(OR(C5="",C7="",C9="",C11="",C13=""),"Missing Info",(C5+C7)*C9*C11/100*C13)


Regards, TMS

[Andrew-R]

There's no easy way using formula to get Excel to tell you if a cell contains a formula or a literal value, you'd need to use a little bit of VB script to give you a User Defined Function (UDF) ...

Please Login or Register  to view this content.

This allows you to use IsFormula as a worksheet function, with it returning TRUE if any of the cells in the range it is passed contain a formula.

[Cidona]

Thank you all for your prompt replies! I had difficultly loggin in to reply yesterday (got message 'Server is busy. Try back latter)

Fotis, I was unable to get your formula to work. When trying to enter the forumla I get the message "The formula you typed contains an error".

adaws. That does exactly what I needed here. Nice and simple which is where I'm at:). Thank you!!

TMShucks. Thank you for your insight. I hadn't realised that it was only testing C5. Not sure why. I thought by having the the "or" in there it would have been looking at each of the C7, C9, C11 and C13. Can you explain why it is only looking at C5? The '=IF(OR(C5="",C7="",C9="",C11="",C13=""),"Missing Info",(C5+C7)*C9*C11/100*C13)' formula works for this use as adaws had also kindly proposed.

When you say 'Similarly, =IF(K5="","",K5) returns a null if K5 is empty but breaks your formula', if I don't put this in C5 then it returns a '0' when K5 is empty and therefore completes the formula in D15 with the 0 value (thereby returning 0 as a result rather than the desired "Missing Info". For this reason it would seem nesscessary to have the '=IF(K5="","",K5)' in C5.

Andrew-R. I tried copying and pasting your code into a module and am now able to enter the function in the worksheet. However due to limitations of my knowledge in this respect I am not sure how to utilze it other than I entered '=IsFormula(C5)', which as you stated returns 'True', I am unsure how to utilize this to get my result in D15. Would it be posible for you to demostrate how the formula could be utilzed in my example as I have a feeling I will soon enough come accross a situation where the other solutions will not work and where this might save the day?

Again thank you all for your time and responses. I have always come away from my posts here with great tricks which I end up utilzing not only for the problem I post but for others situations which pop up that would otherwise have stumpped me.

I will mark the thread as solved as my original problem has been answered, however Andrew-R if you can provide a little more insight on your suggestion it will likely prevent me needing to post a similar post in the future:rolleyes:

Also TMShucks as mentioned if you can explain why my original formula was only looking at C5 even though I had the 'Or' in there would help me have a better understanding.

[TMS]

Your original formula:

=IF(AND(ISBLANK(C5),(C7),(C9),(C11),(C13)),"Missing Info",(C5*C9*(C11/100)*C13)+(C7*C9*(C11/100)*C13))

As you can see, you have ISBLANK(C5) ... so the ISBLANK function is referring to cell C5. The other cells aren't being checked by the ISBLANK.

Note that I shortened the calculation part too, adding C5 and C7 before multiplying by the other cells.

And thanks for the rep.

Regards, TMS

[Cidona]

Ah, I see said the blind man.

I guess if I had really wanted the ISBLANK to look at the (C5),(C7),(C9),(C11),(C13) I'd have had to put =IF(or(ISBLANK(C5),ISBLANK(C7),ISBLANK(C9)....putting in OR rather than AND as well as putting the ISBLANK in front of each of the cell references.

This still wouldn't have gotten around the formula issue which your solution does provide.

Very nice abbreviation of the calculation too! A+ for you TM

Again Thank you very much!!

[TMS]

You're very welcome.

[s0o0y]

I had a somewhat similar scenario I was working on.

I would have never thought of using *cell*="" in place of ISBLANK(*cell*) to bypass the issue of blank cells with imbedded formulas making the ISBLANK function show up FALSE when you wanted it to function as TRUE. Your aid for Cidona's issue has helped me in my own.

Thanks!

[TMS]

@sOoOy: you're welcome. Thanks for the rep. Glad the solution helped you.

Regards, TMS