Formula won't fit

A

a

Is there a way to shorten this formula?

=IF(AND(B13>'Data Entry'!A7,B13<'Data Entry'!B7),1,

IF(AND(B13>'Data Entry'!A8,B13<'Data Entry'!B8),2,

IF(AND(B13>'Data Entry'!A9,B13<'Data Entry'!B9),3,

IF(AND(B13>'Data Entry'!A10,B13<'Data Entry'!B10),4,

IF(AND(B13>'Data Entry'!A11,B13<'Data Entry'!B11),5,

IF(AND(B13>'Data Entry'!A12,B13<'Data Entry'!B12),6,

IF(AND(B13>'Data Entry'!A13,B13<'Data Entry'!B13),7,

IF(AND(B13>'Data Entry'!A14,B13<'Data Entry'!B14),8,

IF(AND(B13>'Data Entry'!A15,B13<'Data Entry'!B15),9,

IF(AND(B13>'Data Entry'!A16,B13<'Data Entry'!B16),10,

IF(AND(B13>'Data Entry'!A19,B13<'Data Entry'!B17),11,12)))))))))))



Thank you
 
F

Frank Kabel

Hi
not completely sure but try the following array formula (entered with
cTRL+sHIFT+ENTER):
=IF(ISNA(MATCH(1,(B13>'Data Entry'!A7:A17)*(B13<'Data
Entry'!B7:B17),0),12,MATCH(1,(B13>'Data Entry'!A7:A17)*(B13<'Data
Entry'!B7:B17),0))
 
H

Harlan Grove

a said:
Is there a way to shorten this formula?

=IF(AND(B13>'Data Entry'!A7,B13<'Data Entry'!B7),1,

IF(AND(B13>'Data Entry'!A8,B13<'Data Entry'!B8),2,

IF(AND(B13>'Data Entry'!A9,B13<'Data Entry'!B9),3,

IF(AND(B13>'Data Entry'!A10,B13<'Data Entry'!B10),4,

IF(AND(B13>'Data Entry'!A11,B13<'Data Entry'!B11),5,

IF(AND(B13>'Data Entry'!A12,B13<'Data Entry'!B12),6,

IF(AND(B13>'Data Entry'!A13,B13<'Data Entry'!B13),7,

IF(AND(B13>'Data Entry'!A14,B13<'Data Entry'!B14),8,

IF(AND(B13>'Data Entry'!A15,B13<'Data Entry'!B15),9,

IF(AND(B13>'Data Entry'!A16,B13<'Data Entry'!B16),10,

IF(AND(B13>'Data Entry'!A19,B13<'Data Entry'!B17),11,12)))))))))))

Unless the 'Data Entry'!A19 reference is a typo and should be 'Data
Entry'!A17, you'll still need a two-part solution. Are you trying to slot
dates in B13 into months? If so, and if B13 contains date values, MONTH(B13)
would be the formula to use.

Next, all inequalities looks like an error. Do you really want 12 as the
result when, say, B13 = 'Data Entry'!A10 ?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top