nested if function problem

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

I have a somewhat complex if statement I am having a
problem with. Figured it would probably be easier to use
a nested if, read the help file for the nested if,
followed the directions, but still is a no go. Here is
the detailed problem.

The are detailed tickets. Tickets are distributed to
registers. From the registers, they are distributed to
the customers. Tickets distrib to registers are on one
worksheet, tickets distributed to customers are on another
sheet. I have performed this & it works out fine &
updates fields appropriately. Tickets are not always
numbered 1 - X, some tickets start with 100 - x for
example.

The problem is..when I distribute tickets to a certain
register & do not sell any of them...and then go to the
sheet where it shows the updated register inventory...i
dont get the number i am looking for

to make it easier for you visually...
Sheet2 - Add permits to registers
Sheet3 - Update permits sold to customers (key in tickets
sold)
Sheet5 - Updated register inventory

Here is the current formula I have in Sheet5..

=IF(MAX(Sheet2!C10:C109)=MAX(Sheet3!C10:C109),MAX(Sheet3!
D10:D108)+1,MAX(Sheet2!C10:C109))

column C = ticket starting #
column D = ticket ending #

i have max( in there because there are many batches that
occur in this process

my current formula in sheet 5 returns to me a value of
1...because there has been no distribution of tickets to
either the register or to customers...when i enter #s to
to the previous distribution, that formula works great!
but when there is no distribution, frustration mounts, if
you need any more details please let me know, this same
msg is posted on the another section as well, appreciate
any help

Dan
Orlando
 
Hi Dan,

I think this is what you're after:

=IF(MAX(Sheet2!C10:C109)=MAX(Sheet3!C10:C109),IF(MAX
(Sheet3!D10:D109)>1,MAX(Sheet3!D10:D109)+1,0),MAX(Sheet2!
C10:C109))

If there are no entries in SHEET3!D10:D109, the formula
will return 0. You can change to whatever you want.

Biff
 
Hey Biff,

You have no idea how much frustration you have saved me.
Thank you! It works. Please help me understand your
statement though.

=if(max(....)=max(....),if(max(...)>1, i am fine up to
here..then what comes next is the value if each are
true... ie MAX(Sheet3!D10:D109)+1 if one or both are
false the value according to your formula is 0. I
understand this, but how can this formula still understand
whatever comes next, ie ,MAX(Sheet2!C10:C109))...from my
prior knowledge i thought the statement would have ended
w/ 0), but your formula works & is correct, if you can
just explain the end of the formula in laymans terms i'd
appreciate it, thanks

Dan
Orlando
 
Hi Dan,

I'll try to explain it!

If max = max is false, the formula skips the second IF
which is the value if max = max is true. In order for the
second IF to be the value if max = max is true, it tests
to see if there are any entries in the range
Sheet3!D10:D109. That's what your formula was missing.

If you don't want the zero value returned, you can change
it to something like "No Sales" or "No Data" or leave it
blank using "".

Biff
 
Sometimes I can't even understand my own explanations!
then max(sheet3!d10:d109) + 1 is called the value if
true (this is the value if both conditions are true
correct?) i believe that is correct...
Yes

then the "" is called the value if one or both
conditions are false correct?
Click to expand...

When cond 2 is false
what makes -max(sheet2!c10:c109)) true or false? what
do you call this statement...value if true, value if
false???
Click to expand...

Cond 1 value if false

Biff
 
Back
Top