General discussion

Locked

Excel Formula

By nhoek ·
Ok I have a formula I would like to use. Hopefull it is possible and hopfully I can explain this right. I have created a spread sheet to test the torque readings of a wrench. Using a torque test device and third party software to transfer the test results to an excel spread sheet. I have 5 numbers that I have to add and then devide them to get the average of the 5 (thats done). I then need to find out if this average is with in plus or minus a percentage of another number. So example.

20+18+20+19+17 =94 /5 = 18.8 (the average) Now then is that 18.8 with in 4% + or - of lets say 20.

My numers are going to be much different depending on what the foot lbs we are testing but for ease I used those numbers. I would also like to display the answer with the number in either green for a pass or red for a fail. Is that possible ????

Hopefull that is explained so you can understand.

Thanks

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by BFilmFan In reply to Excel Formula

This is a pretty easy formula. For the Addition it would:

=(SUM(Cell 1: Cell End)/Count (Cell 1: Cell End))

Example:

=(SUM(A1:A5)/COUNT(A1:A5))

For the +/- factor, you would need a cell to enter the number and then simply use this formula:

It is assumed that:

A7 = percentage variance and you have formatted that cell as a percentage. The formula for the amount of variance would be:

=SUM((A6*A7)) This would be A8

High Side Variance would then be:
=A6+A8

Low Side Variance
=A6-A8 This should be like say B9 for the next part to work:

To apply conditional formatting, select range A1:B9 and choose Format, Conditional Formatting. The Conditional Formatting dialog box will appear with two input boxes. In the first box, choose Formula Is, press Tab, and enter the following formula:

=$B1>$A1
Click Format and choose a format to distinguish the cells (the example uses background shading). Click OK, and the formatting will be applied.

The conditional formatting formula is evaluated for each cell in the range. The trick here is to use mixed cell references (the column references are absolute, but the row references are relative).

Enjoy!

Collapse -

by nhoek In reply to

Poster rated this answer.

Collapse -

by nhoek In reply to Excel Formula

This question was closed by the author

Back to Software Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums