×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Nested "If" Clauses

Nested "If" Clauses

Nested "If" Clauses

(OP)
I would like to know how to put the following into an @if statement:

If b11=.0001, then I want B17, b19 and b21 all to be 0.0001 too.

This has to do with a spreadsheet where I can have up to four "groups".  If I don't have one group, sadly, the variables force me to do division by "0".  So, the best way is to put a 0.0001 into one cell and force the other cells into it too.

RE: Nested "If" Clauses

simple way:

write the same if-clause in all four cells (use b$11 and you can copy the formula)

But this may be a misinterpetation of your problem? You want to exame the content in b11 and if 0.001 then "overwrite" the content of b17,b19..?

If yes then still simple - put your "original" value in a adjacant collumn (e.g. A)

then in the B collumn have this if clause:

=iif(b$11=.0001;0.0001;a17)
and copy this to then rest

all other cell must just have a =aXX formula

Best regards

Morten

RE: Nested "If" Clauses

Are B17, B19, and B21 user inputs or calculated?

If they are user inputs, you need to use the Data>Validation tool to restrict the input.  SHould be straightforward.

If they are computed, you can use =MAX(your formula here, 0.0001).  If the formula value is zero, 0.0001 would be used.

RE: Nested "If" Clauses

To automatically place the value 0.0001 into 3 cells that are normally user inputs, you would have to write a VB macro that would run every time the user changes the value in b11.  You can do this, but there may be a better way, as others have suggested.  

Can you give a little more information?

RE: Nested "If" Clauses

You say that you require cells to equal 0.0001 to stop another formula trying to divide by 0. Why not in the end formula (where divide by 0 occurs), you put an IF statement such as "=if(B11=0,"",xxxxxxxxxxxx)

where al the "x" are, put your orginal formula in. the "" represents a blank cell.

RE: Nested "If" Clauses

You may also consider just checking for zero in the calculated cell:
=IF(A5=0,"A5 must be non-zero",B5/A5)
will display the result of division OR the message as required

Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting

Steam Engine enthusiasts: www.essexsteam.co.uk

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources