C
CelticCharmer
Hi, I just have 2 questions on formulas.
Q1. Can I shorten this formula:
=IF(Sheet2!L5="","",MIN(100,Sheet2!L5))+IF(Sheet2!L6="","",MIN(100,Sheet2!L6))+IF(Sheet2!L7="","",MIN(100,Sheet2!L7))+IF(Sheet2!L8="","",MIN(100,Sheet2!L8))+IF(Sheet2!L9="","",MIN(100,Sheet2!L9))+IF(Sheet2!L10="","",MIN(100,Sheet2!L10))+IF(Sheet2!L11="","",MIN(100,Sheet2!L11))+IF(Sheet2!L12="","",MIN(100,Sheet2!L12))+IF(Sheet2!L13="","",MIN(100,Sheet2!L13))+IF(Sheet2!L14="","",MIN(100,Sheet2!L14))+IF(Sheet2!L15="","",MIN(100,Sheet2!L15))+IF(Sheet2!L16="","",MIN(100,Sheet2!L16))+IF(Sheet2!L17="","",MIN(100,Sheet2!L17))+IF(Sheet2!L19="","",MIN(100,Sheet2!L19))+IF(Sheet2!L20="","",MIN(100,Sheet2!L20))+IF(Sheet2!L21="","",MIN(100,Sheet2!L21))+IF(Sheet2!L22="","",MIN(100,Sheet2!L22))+IF(Sheet2!L23="","",MIN(100,Sheet2!L23))
Q2a. When I use this formula =IF(Sheet2!L5="","",MAX(Sheet2!L5-100)) and
type in a number less than 100 I get a -number(if i type in 98 I get a -2) I
need it to say 0 if the number is less than 100.
Q2b. I need a formula from L5 to L23 and add them together like in Q1, Can
you help?
Thank you
Q1. Can I shorten this formula:
=IF(Sheet2!L5="","",MIN(100,Sheet2!L5))+IF(Sheet2!L6="","",MIN(100,Sheet2!L6))+IF(Sheet2!L7="","",MIN(100,Sheet2!L7))+IF(Sheet2!L8="","",MIN(100,Sheet2!L8))+IF(Sheet2!L9="","",MIN(100,Sheet2!L9))+IF(Sheet2!L10="","",MIN(100,Sheet2!L10))+IF(Sheet2!L11="","",MIN(100,Sheet2!L11))+IF(Sheet2!L12="","",MIN(100,Sheet2!L12))+IF(Sheet2!L13="","",MIN(100,Sheet2!L13))+IF(Sheet2!L14="","",MIN(100,Sheet2!L14))+IF(Sheet2!L15="","",MIN(100,Sheet2!L15))+IF(Sheet2!L16="","",MIN(100,Sheet2!L16))+IF(Sheet2!L17="","",MIN(100,Sheet2!L17))+IF(Sheet2!L19="","",MIN(100,Sheet2!L19))+IF(Sheet2!L20="","",MIN(100,Sheet2!L20))+IF(Sheet2!L21="","",MIN(100,Sheet2!L21))+IF(Sheet2!L22="","",MIN(100,Sheet2!L22))+IF(Sheet2!L23="","",MIN(100,Sheet2!L23))
Q2a. When I use this formula =IF(Sheet2!L5="","",MAX(Sheet2!L5-100)) and
type in a number less than 100 I get a -number(if i type in 98 I get a -2) I
need it to say 0 if the number is less than 100.
Q2b. I need a formula from L5 to L23 and add them together like in Q1, Can
you help?
Thank you