Multiple calculation of range in single cell to find largest number ?

[TG123]

Hi there,

I would like to find the largest and smallest number by doing multiple calculation in a range of data.

Suppose
There are four range A2:A50, B2:B50, C2:C50, D2:50

what I would like to do it

(D2-B2)-(C2+A2)
(D2-B3)-(C2+A3)
(D2-B4)-(C2+A4)
(D2-B5)-(C2+A5)
(D2-B6)-(C2+A6)
(D2-B7)-(C2+A7) and so on..... and at last find the largest of this 50 calculation and put it in cell F2



here, I have attached the file and written all the information about what to do in it....

if any one can help me.

[Crooza]

Hi and Welcome,

In F2 try this

Please Login or Register  to view this content.

but you'll need to remove the "-" from column A, Similarly for G2 you can use the same formula but use the Min function instead

[TG123]

okay great and I got the answer in F2 cell -84.40, but how can I know that its from which combination... like E35, E14... or what ?

update

it's not finding the min value.

Value	Price	Value	Price	Min	Max
3,861.00	7,300.00	123.4	11,200.00
3,672.00	7,500.00	257	11,400.00
2,558.75	7,700.00	432.05	11,600.00

After 1st cell calculation, we get this range
-84.40
-95.40
817.85


and among this -84.40 will be min and 817.85 will be max value

which will be writer under 1st min and max cell


is it right code for min and max ?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Crooza]

Sorry I meant to tell you to use as an array formula which means you need to enter with Control + shift + enter.

[TG123]

awesome it's working great.
now, one last thing.
suppose I got the answer 1516 for max formula, but how can I know from where it derived and from which cells, as double-clicking the cell highlights the whole range as per formula but I want only the answer 1516 to be highlighted from where it got.

[Crooza]

You could use this formula to tell you where it was in the list

Please Login or Register  to view this content.

again array formula so control + shift + enter.
Just check its giving you the right answer as I'm not sure I followed exactly what you were after

[TG123]

its giving me circular reference error

and when tried in another cell its give me 0 answers

i think you missed to write the max,
is it right ?

Please Login or Register  to view this content.

[Crooza]

You'll need to put it in another cell. Leave F2 as it is and put that formula in a cell (say F1) I just want to see if it works. If it does we can look at combining the two then

[TG123]

yes, tried it in another cell but its not working

[Crooza]

See attached

[TG123]

yes, got it...

all query got solved....

thank you so much...