Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-21-2009, 01:36 PM
gmyls gmyls is offline division zero escape? Windows XP division zero escape? Office XP
Novice
division zero escape?
 
Join Date: May 2009
Posts: 2
gmyls is on a distinguished road
Default division zero escape?

Hello friends!
I'm new here and I have a little problem in my Excel worksheet.

I want to know if there is a way to , by setting the value of e.g A1 to zero, inactivate terms in a formula containing/related to this specific field A1 ?


Let us say that I have a formula giving me the time for a procedure depending on some preset values in other fields.
it could look like this
e.g C20

=C3*C4*C5*(C6/C7+C8+C9)



where c3,c4,c5 are constants and the terms inside the brackets are some functions that CAN become divided by zero.

E.G the C8 might be a function depending on the value in field A1, however, in my case there will only be values on one of the terms inside the brackets at the same time.
It could look like this C3*C4*C5*(C6/C7+C8+C9)
orC3*C4*C5*(C6/C7+C8+C9) or C3*C4*C5*(C6/C7+C8+C9)
where the red marked functions I wish to be "inactivated" if there is a zero in a specific field.

This wish comes from the fact that this funtion describes the procedure when building a brick-wall. In the procedure I've isolated three kinds of different operations (like where you put the brick) and they all contribute to the time-elapse in different ways.
However, like I mentioned before, there is only one kind of operation taking place at the same time. But I still want them all to be included in the same formula just that I want them to be omitted if I tell a certain filed: be zero!

So the question again: can a term in a formula be omitted from the calculation eventhough it still stands visible in the formula? (e.g. by telling a field connected to the term to be zero) can for example the term become grey-marked? I've heard that that is a sign of inactivation?

glad for help!
Sincerely gmyls!

(if it would be to any help I can Email the worksheet)
Reply With Quote
  #2  
Old 05-22-2009, 02:08 AM
zyzzyva57 zyzzyva57 is offline division zero escape? Windows 7 32bit division zero escape? Office 2007
Expert
 
Join Date: Mar 2009
Location: Dawsonville, Ga (NE of Atl)
Posts: 355
zyzzyva57 is on a distinguished road
Default

Hope this helps some

Not knowing fully how your spreadsheet looks, so what I did is the following:

At my top, I have my inputs which I would change with my needs

I use Conditional Formatting to spot zeros

so with your formula, I did this

=IF(C6=0,(C3*C4*C5)*(C7+C8+C9),C3*C4*C5*(C6/C7+C8+C9))

which does NOT do a calculation with C6 if its value is a zero, BUT does a calculation without C6

Obviously, if I am in the ballpark with this solution, check carefully my formula

Hopes this helps to get you started
Attached Files
File Type: xls ZeroTest.xls (42.5 KB, 14 views)
Reply With Quote
  #3  
Old 05-22-2009, 02:37 AM
gmyls gmyls is offline division zero escape? Windows XP division zero escape? Office XP
Novice
division zero escape?
 
Join Date: May 2009
Posts: 2
gmyls is on a distinguished road
Default

Thanks for your answer zyzzyva57!

This seems to be in the right direction!

In my formula there is a problem that if C7 (in your file) equals to zero then the total result will contain a division by zero. so I tried to experiment in your formula so that C7 would be omitted if it gets the value zero and it worked!

So now there is actually only a small problem left. I shall try to use that nice "conditional formating" of yours in my own spreadsheet. Being a n00b I'm not sure that I'll fix it but I shall try!

One question I have directly is: Can I choose to omit more than one term? e.g can I choose to omit even the C8-term (if being zero) in the same line as this: =OM(C7=0;(C3*C4*C5)*(C8+C9);C3*C4*C5*(C6/C7+C8+C9)) ?

(by the way.. what does OM mean ?)

thanks again /gmyls
Reply With Quote
Reply

Tags
inactivate, make invisible

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
special/escape/insertion characters in word manojbmsce Word 0 09-25-2008 06:40 AM
escape characters LadyReader Word VBA 0 09-06-2006 12:09 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:44 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft