Calculation according to Assign Grid

earthworm · 10-13-2009, 01:17 PM

Hi Guys ,

I Have a DATA based on the grid according to the values which will be put
ting

Like if A = 0 AND U = 1 I Want the marks to be shows as 1

but if the A=1 And U = 1 I want the marks to be shown as 0

And if A = 2 AND U = 2 I want the marks to be shown according to grid

and if A=2 And U = 1 I want the marks to be shown as -2

i Tried one formula but i m not satisfied with the result plus also i dont want

value to be shown as 2 if no values are entered in A or U

WAITING

Sample excel is attached

**martindwilson** · 10-13-2009, 01:31 PM

Waiting ?????????

**NBVC** · 10-13-2009, 01:32 PM

I am confused...

Are you just wanting to lookup A and U values in the grid and return the Marks values?

**martindwilson** · 10-13-2009, 01:42 PM

thats what i think just add all the possible values to grid and look them up
try adding 0 1 1
1,1,0 and so on to grid then in
d5

=IF(OR(B5="",C5=""),"",INDEX($H$6:$H$20, MATCH(B5 & C5, INDEX(F6:F20 & G6:G20, 0), 0)))

earthworm · 10-13-2009, 02:16 PM

i want to the marks to calculate according to grid.

LIKE A = 0 AND B = 0 RESULT = 2

A = 0 AND B = 1 RESULT = 1

I tried this formula but when i enter 1 in a and 2 in b it shows #n/a

=IF(OR(B5="",C5=""),"",INDEX($H$6:$H$20, MATCH(B5 & C5, INDEX(F6:F20 & G6:G20, 0), 0)))

A = 2 B = 2 RESULT -2 and onwards

Please share the excel . or check my formula if i m doing right ?

i m confused that when i enter 4 in a and 0 in B Is it showing me the right result according to grid ?

**NBVC** · 10-13-2009, 02:19 PM

Try:

=IF(OR(B5="",C5=""),"",SUMPRODUCT(--($F$6:$F$18=B5),--($G$6:$G$18=C5),H6:H18))

**martindwilson** · 10-13-2009, 03:18 PM

you have no value of 1,2 in grid! to match
the sumproduct offered by nbvc will ignore that,and return 0 which is probably what you want

earthworm · 10-13-2009, 09:36 PM

Originally Posted by martindwilson

you have no value of 1,2 in grid! to match
the sumproduct offered by nbvc will ignore that,and return 0 which is probably what you want

OK Thanks for your help

can you please explain this formula

SUMPRODUCT(--($F$6:$F$18=B5),--($G$6:$G$18=C5),H6:H18))

" -- " ARRAY . What does it do and is it same like " AND " Formula ?

Can you please share the excel with simple array formula to complex for me to understand the concept.

Secondly the formula u gave please explain the meaning