2 questions.

[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

 

[Bernie Deitrick]

CC,

First one:

Array enter (enter using Ctrl-Shift-Enter)

=SUM(IF(Sheet2!L5:L23="","",IF(Sheet2!L5:L23>100,100,Sheet2!L5:L23)))

2a:

=IF(Sheet2!L5="","",IF(Sheet2!L5<100,0,L5-100))

2b)

Array enter (enter using Ctrl-Shift-Enter)

=SUM(IF(Sheet2!L5:L23="","",IF(Sheet2!L5:L23<100,0,Sheet2!L5:L23-100)))

HTH,
Bernie
MS Excel MVP

 

[CelticCharmer]

Hello and thank you Bernie,
The first one works brill thank you ,,,,,
The second 2a & 2b don't am I doing something wrong?
If I type in 99 it show as 0 but once I type in 100 it shows -100 I want it
to saty at 0. When I type in 101 it still shows -100 but I want it to show 1.
If i type in say 150 I want it to show 50. (anything above 100 I want it to
show)
again thank you

 

[Bernie Deitrick]

Did you enter this? Are you entering the value in L5 - (And perhaps you are
using your _old_ formula....)

=IF(Sheet2!L5="","",IF(Sheet2!L5<100,0,L5-100))

When I enter 100, I get 0, 101 and I get 1, 150 I get 50, etc. And the same
for the array formula....

Bernie
MS Excel MVP

 

[CelticCharmer]

Ok it is working now.
Thank you very much Bernie.