how to sum up cells and multiply by 10, min 10 max 100?

Hi all.

I'll try to explain this as best as I can. I'm trying to figure out a formula that will sum up several cells and multiply that by 10, however the minimum figure must be 10 and the maximum 100.


For example:

A B C D E F
50 1 1 1 1 1

If I have five cells with one it would calculate to 50 in cell A.

However, if the sum times 10 is say 5 I need it to be 10, and if the sum times 10 is 200 I need it to be a maximum of 100.

Something like sum of cells multiplied by 10, if less than 10 than put 10, if more than 100 than put 100.

Hope I explained it clearly enough, thanks!

Your post does not comply with Rule 1 of our Forum Rules. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

How about in column G

=IF(SUM(A1:F1)*10<10,10,IF(SUM(A1:F1)*10>100,100,???))

Replace ??? with whatever you want the result to be if SUM(A1:F1)*10 > 10 and < 100 as you havent specified that

See reply above from Special-K