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="","",MI N(100,Sheet2!L7))+IF(Sheet2!L8="","",MIN(100,Sheet 2!L8))+IF(Sheet2!L9="","",MIN(100,Sheet2!L9))+IF(S heet2!L10="","",MIN(100,Sheet2!L10))+IF(Sheet2!L11 ="","",MIN(100,Sheet2!L11))+IF(Sheet2!L12="","",MI N(100,Sheet2!L12))+IF(Sheet2!L13="","",MIN(100,She et2!L13))+IF(Sheet2!L14="","",MIN(100,Sheet2!L14)) +IF(Sheet2!L15="","",MIN(100,Sheet2!L15))+IF(Sheet 2!L16="","",MIN(100,Sheet2!L16))+IF(Sheet2!L17="", "",MIN(100,Sheet2!L17))+IF(Sheet2!L19="","",MIN(10 0,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!L2 3="","",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

CC,

First one:

Array enter (enter using Ctrl-Shift-Enter)

=SUM(IF(Sheet2!L5:L23="","",IF(Sheet2!L5:L23100,1 00,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" wrote in message
...
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="","",MI N(100,Sheet2!L7))+IF(Sheet2!L8="","",MIN(100,Sheet 2!L8))+IF(Sheet2!L9="","",MIN(100,Sheet2!L9))+IF(S heet2!L10="","",MIN(100,Sheet2!L10))+IF(Sheet2!L11 ="","",MIN(100,Sheet2!L11))+IF(Sheet2!L12="","",MI N(100,Sheet2!L12))+IF(Sheet2!L13="","",MIN(100,She et2!L13))+IF(Sheet2!L14="","",MIN(100,Sheet2!L14)) +IF(Sheet2!L15="","",MIN(100,Sheet2!L15))+IF(Sheet 2!L16="","",MIN(100,Sheet2!L16))+IF(Sheet2!L17="", "",MIN(100,Sheet2!L17))+IF(Sheet2!L19="","",MIN(10 0,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!L2 3="","",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

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" wrote:

CC,

First one:

Array enter (enter using Ctrl-Shift-Enter)

=SUM(IF(Sheet2!L5:L23="","",IF(Sheet2!L5:L23100,1 00,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" wrote in message
...
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="","",MI N(100,Sheet2!L7))+IF(Sheet2!L8="","",MIN(100,Sheet 2!L8))+IF(Sheet2!L9="","",MIN(100,Sheet2!L9))+IF(S heet2!L10="","",MIN(100,Sheet2!L10))+IF(Sheet2!L11 ="","",MIN(100,Sheet2!L11))+IF(Sheet2!L12="","",MI N(100,Sheet2!L12))+IF(Sheet2!L13="","",MIN(100,She et2!L13))+IF(Sheet2!L14="","",MIN(100,Sheet2!L14)) +IF(Sheet2!L15="","",MIN(100,Sheet2!L15))+IF(Sheet 2!L16="","",MIN(100,Sheet2!L16))+IF(Sheet2!L17="", "",MIN(100,Sheet2!L17))+IF(Sheet2!L19="","",MIN(10 0,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!L2 3="","",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

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" wrote in message
...
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" wrote:

CC,

First one:

Array enter (enter using Ctrl-Shift-Enter)

=SUM(IF(Sheet2!L5:L23="","",IF(Sheet2!L5:L23100,1 00,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" wrote in
message
...
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="","",MI N(100,Sheet2!L7))+IF(Sheet2!L8="","",MIN(100,Sheet 2!L8))+IF(Sheet2!L9="","",MIN(100,Sheet2!L9))+IF(S heet2!L10="","",MIN(100,Sheet2!L10))+IF(Sheet2!L11 ="","",MIN(100,Sheet2!L11))+IF(Sheet2!L12="","",MI N(100,Sheet2!L12))+IF(Sheet2!L13="","",MIN(100,She et2!L13))+IF(Sheet2!L14="","",MIN(100,Sheet2!L14)) +IF(Sheet2!L15="","",MIN(100,Sheet2!L15))+IF(Sheet 2!L16="","",MIN(100,Sheet2!L16))+IF(Sheet2!L17="", "",MIN(100,Sheet2!L17))+IF(Sheet2!L19="","",MIN(10 0,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!L2 3="","",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

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

"Bernie Deitrick" wrote:

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" wrote in message
...
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" wrote:

CC,

First one:

Array enter (enter using Ctrl-Shift-Enter)

=SUM(IF(Sheet2!L5:L23="","",IF(Sheet2!L5:L23100,1 00,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" wrote in
message
...
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="","",MI N(100,Sheet2!L7))+IF(Sheet2!L8="","",MIN(100,Sheet 2!L8))+IF(Sheet2!L9="","",MIN(100,Sheet2!L9))+IF(S heet2!L10="","",MIN(100,Sheet2!L10))+IF(Sheet2!L11 ="","",MIN(100,Sheet2!L11))+IF(Sheet2!L12="","",MI N(100,Sheet2!L12))+IF(Sheet2!L13="","",MIN(100,She et2!L13))+IF(Sheet2!L14="","",MIN(100,Sheet2!L14)) +IF(Sheet2!L15="","",MIN(100,Sheet2!L15))+IF(Sheet 2!L16="","",MIN(100,Sheet2!L16))+IF(Sheet2!L17="", "",MIN(100,Sheet2!L17))+IF(Sheet2!L19="","",MIN(10 0,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!L2 3="","",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