nesting functions (I think)

[Pamela]

I am trying to set up a formula (function) so that I can
add the values in several cells in a row and, depending on
the actual value, give different results.

Well, I'm sure that's clear as mud. For example..

I want to add the values in cells I3, J3, and K3 and place
the result in L3; the values in the cells may be "blank"
(meaning the formula to calculate that cell's value is
there but there is no actual data), negative, or positive
values. If the value for L3 is either "iserror" or "<=0" I
want L3 to be blank (,"",); otherwise, I want the actual
value. I can get the function to give me either but not
both of these options.

does anyone get what I am asking? Do you have a solution?
I'm a relative novice with Excel and certainly with
newsgroups...please be patient with me. thanks.

 

[J.E. McGimpsey]

I am trying to set up a formula (function) so that I can
add the values in several cells in a row and, depending on
the actual value, give different results.

Well, I'm sure that's clear as mud. For example..

I want to add the values in cells I3, J3, and K3 and place
the result in L3; the values in the cells may be "blank"
(meaning the formula to calculate that cell's value is
there but there is no actual data), negative, or positive
values. If the value for L3 is either "iserror" or "<=0" I
want L3 to be blank (,"",); otherwise, I want the actual
value. I can get the function to give me either but not
both of these options.

does anyone get what I am asking? Do you have a solution?
I'm a relative novice with Excel and certainly with
newsgroups...please be patient with me. thanks.

If you use:

L3: =IF(SUM(I3:K4)<=0, "", SUM(I3:K3))

I think you'll get what you're after. SUM() ignores text values,
including null strings (""), while the + operator gives a #VALUE!
error when attempting to add text. Unless you're getting error
values in I3:K3, you shouldn't need to use ISERROR().

 

[Pamela]

-----Original Message-----


If you use:

L3: =IF(SUM(I3:K4)<=0, "", SUM(I3:K3))

I think you'll get what you're after. SUM() ignores text values,
including null strings (""), while the + operator gives a #VALUE!
error when attempting to add text. Unless you're getting error
values in I3:K3, you shouldn't need to use ISERROR().
.

You are right there with me...glad it made some sense. I,
unfortunately, do get error values in I3:K3 because when
there is no data for the cell, I get #value! errors. Is
there a way to add both <=0 and iserror criteria? thanks.

 

[J.E. McGimpsey]

You are right there with me...glad it made some sense. I,
unfortunately, do get error values in I3:K3 because when
there is no data for the cell, I get #value! errors. Is
there a way to add both <=0 and iserror criteria? thanks.

I'd recommend dealing with (getting rid of) the error values in
I3:K3 instead.

In general, it's *very* poor design to propagate errors on the sheet
- it both desensitizes users to "non-design" errors elsewhere in the
sheet, and masks errors that were not anticipated by the designer.

What are the formulae in I3:K3? Under what circumstances do they
return #VALUE! errors?



If you absolutely cannot modify the sheet to get rid of the #VALUE!
errors, you could use something like:

L3: =IF(ISERR(SUM(I3:K3)),"",IF(SUM(I3:K3)<=0,"", SUM(I3:K3)))

 

[Pamela]

-----Original Message-----


I'd recommend dealing with (getting rid of) the error values in
I3:K3 instead.

In general, it's *very* poor design to propagate errors on the sheet
- it both desensitizes users to "non-design" errors elsewhere in the
sheet, and masks errors that were not anticipated by the designer.

What are the formulae in I3:K3? Under what circumstances do they
return #VALUE! errors?



If you absolutely cannot modify the sheet to get rid of the #VALUE!
errors, you could use something like:

L3: =IF(ISERR(SUM(I3:K3)),"",IF(SUM(I3:K3)<=0,"", SUM(I3:K3)))
.
thanks...this really helps for now; I'm working from

someone elses workbook with old macros and confusing
references. I know I need to clean it up but as an Excel
"newbie" this gives me a bandaid until I can do surgery!

Are you a Microsoft Tech support person? What's the best
way for me to get help with redeveloping this workbook?

thanks for your support.

 

[J.E. McGimpsey]

thanks...this really helps for now; I'm working from
someone elses workbook with old macros and confusing
references. I know I need to clean it up but as an Excel
"newbie" this gives me a bandaid until I can do surgery!

That's certainly understandable .

Are you a Microsoft Tech support person? What's the best
way for me to get help with redeveloping this workbook?

No - I'm what's called a Microsoft MVP - MVPs can't be employees. See

http://mvp.support.microsoft.com/default.aspx?scid=fh;EN-US;mvpfaqs

If you're tackling the redesign yourself, and you want assistance,
you can certainly continue to post to the newsgroups (take a look in
the archives, too:

http://groups.google.com/advanced_group_search?q=group:*mac.office*

there's often an answer that's just waiting for you). If you want to
contract it out, I and others do commercial work as well - I can
suggest a few names if you wish.

 

[Pamela]

Thanks...these sites are great and I am even more
appreciative of your help now that I know what an MVP is!
The *mac.office* site was very helpful but, alas, I am
still having a problem with the cells that have errors in
them. I am very interested in finding out about
contracting out part of this project but it is a personal,
not work related, speadsheet. Without getting into too
much detail, let me briefly explain what I am trying to do
and what my current problem is; please let me know if this
is a "contracting out" type of project and if you (or
anyone else) have any suggestions for my current problem, OK?

This speadsheet tracks diabetic data including blood
glucose levels (BG), carbohydrate intake (CHO), insulin
doses, and formulas using the BG and CHO data to calculate
the insulin doses.

Here's what I have:
A=date
B=time (24 hours/day)
C=BG
D=CHO
E=CHO/insulin ratio (divide CHO by xx to get insulin dose)
F= "meal bolus" =IF(ISERROR(D3/E3),"",(D3/E3) (amount of
insulin determined in E3)
G= "insulin sensitivity"
=IF(ISERROR((C3-140)/H3),"",((C3-140)/H3) (amount of
insulin it takes to drop BG to 140 - can be a positive or
negative value)
H= "calculated correction bolus" Calculated insulin dose
determined in H3.
I= "wizard correction bolus" =IF(H3="","",(H3-0.05)) -
variation of I3 based on correction factor of -0.05
J= "active insulin" (value entered as raw data from a piece
of equipment (insulin pump) [eventually I'd like to add the
formula to calculate this but it is complex so for now I'm
just entering the data - can be a positive or negative value.
K= "total correction bolus"
=IF(ISERR(SUM(I3:J3)),"",IF(SUM(I3:J3)<=0,"", SUM(I3:J3)))

HERE IS WHERE I RUN INTO TROUBLE...

L= "total actual bolus" should be F3+K3; the problem is
that in order to keep cells empty when there is no data in
them, they go to the "ISERROR(X),"",(X)" and then provide a
#value! error in L.

there are 2 hidden sheets in the workbook; one is "data
only" that has just the raw data but I don't know how to
track how that data gets there...I'm sure it's a macro but
I don't know how to work with them. The other is "values
only" that gets its data from the sheet I just described.

I think the problem may be on the "data only" sheet...

any thoughts/suggestions welcome! thanks for your input.

 

[Bob Greenblatt]

Thanks...these sites are great and I am even more
appreciative of your help now that I know what an MVP is!
The *mac.office* site was very helpful but, alas, I am
still having a problem with the cells that have errors in
them. I am very interested in finding out about
contracting out part of this project but it is a personal,
not work related, speadsheet. Without getting into too
much detail, let me briefly explain what I am trying to do
and what my current problem is; please let me know if this
is a "contracting out" type of project and if you (or
anyone else) have any suggestions for my current problem, OK?

This speadsheet tracks diabetic data including blood
glucose levels (BG), carbohydrate intake (CHO), insulin
doses, and formulas using the BG and CHO data to calculate
the insulin doses.

Here's what I have:
A=date
B=time (24 hours/day)
C=BG
D=CHO
E=CHO/insulin ratio (divide CHO by xx to get insulin dose)
F= "meal bolus" =IF(ISERROR(D3/E3),"",(D3/E3) (amount of
insulin determined in E3)
G= "insulin sensitivity"
=IF(ISERROR((C3-140)/H3),"",((C3-140)/H3) (amount of
insulin it takes to drop BG to 140 - can be a positive or
negative value)
H= "calculated correction bolus" Calculated insulin dose
determined in H3.
I= "wizard correction bolus" =IF(H3="","",(H3-0.05)) -
variation of I3 based on correction factor of -0.05
J= "active insulin" (value entered as raw data from a piece
of equipment (insulin pump) [eventually I'd like to add the
formula to calculate this but it is complex so for now I'm
just entering the data - can be a positive or negative value.
K= "total correction bolus"
=IF(ISERR(SUM(I3:J3)),"",IF(SUM(I3:J3)<=0,"", SUM(I3:J3)))

HERE IS WHERE I RUN INTO TROUBLE...

L= "total actual bolus" should be F3+K3; the problem is
that in order to keep cells empty when there is no data in
them, they go to the "ISERROR(X),"",(X)" and then provide a
#value! error in L.

there are 2 hidden sheets in the workbook; one is "data
only" that has just the raw data but I don't know how to
track how that data gets there...I'm sure it's a macro but
I don't know how to work with them. The other is "values
only" that gets its data from the sheet I just described.

I think the problem may be on the "data only" sheet...

any thoughts/suggestions welcome! thanks for your input.

There are several of us that are interested in contract work. To provide you
with a firm bid, we (at least I do) need to see the workbook and have a
clear explanation of what you want.

As a suggestion, one way I have used to get rid of pesky errors is to format
the font to white. Then you may be able to use conditional formatting to
turn the "real" values to black (or some other color.)

 

[J.E. McGimpsey]

There are several of us that are interested in contract work. To provide you
with a firm bid, we (at least I do) need to see the workbook and have a
clear explanation of what you want.

I couldn't agree more. I have a Standard rate that I use when
working with a customer to develop a specification. If the
specification is provided, I can bid a substantial discount.