IF FORMULA - is this scenario possible

[dunmore83]

HI,

TRYING TO MAKE AN AUTOMATED SPREADSHEET.

HAVE THE FOLLOWING SCENARIO:

- If cell I11 has a numerical value then cells L6:L9 should be all 0 values

- If cell I11 has a "N/A" value then cells L6:L9 should also be all 0 values

The catch is in the bottom scenario (i.e. if I11 is "N/A") then i want to be able to enter numerical values in L6:L9.

However if ILL has the "numerical value" then cells L6:L9 should all be 0 values regardless.

Is this possible??

Thanks so much!!!

 

[Shweta]

Hi Dunmore83,

Can you put some data here?

What I am getting by your post is you want 0 in L6:L9 in both the cases if I11 is numeric or #N/A.

Shweta

 

[dunmore83]

Yes that is correct....

however,

if I11 is a 'numerical value' then L6:L9.... MUST be all 0's i.e $0.0000

BUT

if I11 is "N/A" then L6:L9 require data to be inputed manually
e.g. $1.6386, $1.6476, $1.5247, $1.8745 (these numbers will differ everytime though)

Hope it makes sense!

Cheers

 

[Shweta]

Try this in L6:L9

=IF(ISNUMBER($I$11),0,IF(ISERROR($I$11)=TRUE,0,))

Hope this will work

change the formattting to $0.0000

Shweta

 

[dunmore83]

Thanks for your reply, it works but does not completely satisfy my requirements.

To reiterate;

- if I11 is a 'numerical value' say L6 'must be 0'

- however, if I11 is "N/A" then i need to enter a number (i.e $1.6524) in cell L6 manually

This may not be possible since whatever number i enter in cell L6 (if I11 is N/A) will overwrite the formula.

Thoughts???

 

[Shweta]

I got you but when you enter a formula in a cell, nothing can be entered manually in that cell and if you enter manually, formula will get overwrited definitely.

 

[dunmore83]

thats what i thought

cheers anyway!!