excel error

[thestones757]

I am using a max function to find the max of an array found by multiplying two conditions to arrive at a single array:

=MAX((Data!$B$3:$B$202=Analysis!$B4)*(Data!F$3:F$202))

where the first condition is used as a true false (1 or 0) to only count the following condition when it is met. It works fine and is doing what I want it to do, however, there is a computational error within Excel.

I have data listed on a previous tab (Data) in F3:F202 and my formula builder will give me the correct maximum from the data, however, it displays a different number than this number in the cell.

It does not make sense why this isn't replicating from the formula builder and appearing correctly on my spreadsheet. If anyone has dealt with or might have a solution it would be greatly appreciated!

 

[Aladin Akyurek]

You need to confirm such a formula with control+shift+enter, not just with enter. By the way, the IF syntax is a tad faster:

=MAX(IF(Data!$B$3:$B$202=Analysis!$B4,Data!F$3:F$202))

which you need also to confirm with control+shift+enter.

 

[thestones757]

that is spectacular, thank you. why is that though? like when do I know to do that?

 

[Aladin Akyurek]

that is spectacular, thank you.

Thanks for providing feedback.

why is that though? like when do I know to do that?

Almost whenever you need to evaluate an object (a range, for example) all at once (instead of bit by bit or per cell)... In fact, technically when a range object is transformed into an array obeject.

The equality test...

Data!$B$3:$B$202=Analysis!$B4

evaluates to an array object of TRUE's and FALSE's. More often than not, such must be signalled to Excel. That's done with: control+shift+enter.

See also "array formulas" in Excel's help.

 

[thestones757]

Well thanks a bunch, I really appreciate your time. Hopefully I won't run into any future problems with this info, once again thanks for your continued help.

Have a nice day.

 

[Aladin Akyurek]

Well thanks a bunch, I really appreciate your time. Hopefully I won't run into any future problems with this info, once again thanks for your continued help.

You are welcome.

Have a nice day.

You too...