Replies

X2: 13-Jun-11

Y2:

=SUMIF(Date_ordered,X2,Price)

should yield a total rekated to X2.

If you enter 30-Jun-11 in X3, the formula can be copied down to obtain the total for X3.

Are we missing something?

Can you explain what the 9.99E+307 represents in the formula?

Here is a b it technical explanation:

http://www.mrexcel.com/forum/showthread/?t=310278

I guess the answer is that the data in the search range needs to be in ascending order to get useful results. In my case, it is not. Don’t think I can use VLOOKUP either. Hmmmm.

Thanks … John

Try…

=IF(C44=1;INDEX($A24:$A40;MATCH(C43;C24:C40;0));””)

Excel 2003

Hi

Cell A10 of the attached have this formula which return correctly the text contain in a cell of the range
However, am not sure if this is a valid formula?

=LOOKUP(“text”,A1:A9)

If I change the text to tv3 in the cell A4, its return #N/A, why is this so?

Need your expertise on this

TIA

cheers, francis

LOOKUP requires that the range it applies to is sorted in ascending order. This function most probably uses a variant of binary serach.

How that works is described in a graphical manner in the following attempt:

http://tinyurl.com/czkket

How to have the number of last free cell in range A2:IV2…
In this my case 4 (D2 is the free last cell)

Doing it by means of a formula:

Control+shift+enter, not just enter…

=MATCH(2,1/(1-ISBLANK(A2:IV2)))+1

If A2:IV2 is empty (unused), the result will be #N/A.

Observe that I added another record to the sample you provided.

Sheet2…

B1: 30

B2: 1

B3:

Control+shift+enter…

=SUM(IF(Sheet1!C2:C6=B1,IF(Sheet1!D2:D6=MIN(IF(Sheet1!C2:C6=B1,Sheet1!D2:D6)),1)))

A5:

Control+shift+enter…

=IF(ROWS($A$5:A5)<=$B$3,SMALL(IF(Sheet1!$C$2:$C$6=$B$1,IF(Sheet1!$D$2:$D$6=MIN(IF(Sheet1!$C$2:$C$6=$B$1,Sheet1!$D$2:$D$6)),ROW(Sheet1!$D$2:$D$6)-ROW(Sheet1!$D$2)+1)),ROWS($A$5:A5)),"")

and copy down.

B5, copy across then down:

=IF(N($A5),INDEX(Sheet1!A$2:A$6,$A5),"")

Since you are on Excel 2003, try to exploit the Data|List|Create List functionality.

Calling

=SUMPRODUCT((X=x)*(Y=y),Z)

‘more robust’ compared with:

{=SUM(IF(X=x,IF(Y=y,Z)))}

tends to have an unfortunate implication that:

{=MAX(IF(X=x,IF(Y=y,Z)))}

{=AVERAGE(IF(X=x,IF(Y=y,Z)))}

and similar multiconditional formulae are ‘less robust’, ‘non-robust’, etc.

Suspected that much. But, that notion of robustness is unfortunate for it qualifies all multicondinoal formulae involving MAX, MIN, AVERAGE, MEDIAN, etc. as non-robust.

Now you know how the ROW(INDIRECT(…)) bit works, isn’t that a too expensive formula?

An alternative would be:

=TRIM(RIGHT(” “&A2,LEN(” “&A2)-FIND(“@”,SUBSTITUTE(” “&A2,” “,”@”,LEN(” “&A2)-LEN(SUBSTITUTE(” “&A2,” “,””))))))

which just needs enter. BTW, the string in A2 should not have trailing spaces.

=SUMPRODUCT(–(MOD(COLUMN(B2:BB2)-COLUMN(B2)+0,2)=0),B2:BB2)

Why is a SumProduct formula ‘more robust’?

You’re right. You can reduce the formula to:

=($A2″”)*(LOOKUP($A2+$F$1,GTable)LOOKUP($A2,GTable))

Not sure I’ve got the story right, but try:

1) Activate Insert|Name|Define.
2) Enter GTable as name in the Names in Workbook box.
3) Enter the following formula in the Refers to box:

={0,”F”;70,”C”;77,”C+”;80,”B”;87,”B+”;90,”A”}

Click OK.

A) Select A2:A10 (adjust to suit), the range housing numeric scores.
Activate Format|Conditional Formatting.
C) Choose Formula Is for Condition 1.
D) Invoke the following formula:

=($A2″”)*(LOOKUP(MIN($A2+$F$1,100),GTable)LOOKUP($A2,GTable))

E) Activate the Format button and apply apropriate formatting (pink and dots)
F) Click OK, OK.

BTW: Since we named the relevant table, we can change the original formula:

=LOOKUP(MIN(A2+$F$1,100),{0;70;77;80;87;90},{“F”;”C”;”C+”;”B”;”B+”;”A”})

To:

=LOOKUP(MIN(A2+$F$1,100),GTable)

Fred,

Just to make sure:

A2: 50, modified 50+$F$1=51

A3: 86, modified 86+$F$1=87

A4: 77, modified 77+$F$1=78

Could you specify what colors would apply to A2:A4, specified per cell?

Aladin