Hi. I have been trying to get my head around some formulas and it has been
driving me crazy (yea, one of them beginners, me!!)

Basically, I need to add numbers from a range of cells, but do not want the
result to exceed a certain value. For example, I want the sum of cells A1,
A2, A4, B5 & B6 to be displayed in cell C15, however, if the sum is more than
128, it should pop up with an error box asking the user to re-evaluate the
numbers entered in the cells to be calculated.

I need to use this to send to my clients to fill in the numbers and send
back to me, so I also want to know if it is possible to protect the formulas
entered, even if they save it as another file.

Any help really, really appreciated.

Cheers
Ken

Hi Ken:

Try:

=IF(SUM(A1,A2,A4,B5,B6,)128,"ERROR",SUM(A1,A2,A4, B5,B6,))

and have a good day.
___________________________
Gary's Student


"KenNZ" wrote:

Hi. I have been trying to get my head around some formulas and it has been
driving me crazy (yea, one of them beginners, me!!)

Basically, I need to add numbers from a range of cells, but do not want the
result to exceed a certain value. For example, I want the sum of cells A1,
A2, A4, B5 & B6 to be displayed in cell C15, however, if the sum is more than
128, it should pop up with an error box asking the user to re-evaluate the
numbers entered in the cells to be calculated.

I need to use this to send to my clients to fill in the numbers and send
back to me, so I also want to know if it is possible to protect the formulas
entered, even if they save it as another file.

Any help really, really appreciated.

Cheers
Ken

You can use Gary's formual and the apply a validation to get the pop up box

Highligh the column that you put the formula in:
go to Data--validation
select custom

put formula="error"
on the next tab error Alert Type the message that should be displayed when
there is error in the cell. click ok.

Nikki

"KenNZ" wrote:

Hi. I have been trying to get my head around some formulas and it has been
driving me crazy (yea, one of them beginners, me!!)

Basically, I need to add numbers from a range of cells, but do not want the
result to exceed a certain value. For example, I want the sum of cells A1,
A2, A4, B5 & B6 to be displayed in cell C15, however, if the sum is more than
128, it should pop up with an error box asking the user to re-evaluate the
numbers entered in the cells to be calculated.

I need to use this to send to my clients to fill in the numbers and send
back to me, so I also want to know if it is possible to protect the formulas
entered, even if they save it as another file.

Any help really, really appreciated.

Cheers
Ken

sorry, I just realized this will give error message to everything. you can
set up validation to restrict value between 0 and 128. everything else will
give you an error.

"Nikki" wrote:

You can use Gary's formual and the apply a validation to get the pop up box

Highligh the column that you put the formula in:
go to Data--validation
select custom

put formula="error"
on the next tab error Alert Type the message that should be displayed when
there is error in the cell. click ok.

Nikki

"KenNZ" wrote:

Hi. I have been trying to get my head around some formulas and it has been
driving me crazy (yea, one of them beginners, me!!)

Basically, I need to add numbers from a range of cells, but do not want the
result to exceed a certain value. For example, I want the sum of cells A1,
A2, A4, B5 & B6 to be displayed in cell C15, however, if the sum is more than
128, it should pop up with an error box asking the user to re-evaluate the
numbers entered in the cells to be calculated.

I need to use this to send to my clients to fill in the numbers and send
back to me, so I also want to know if it is possible to protect the formulas
entered, even if they save it as another file.

Any help really, really appreciated.

Cheers
Ken