Formulas

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.