[Boards: 3 / a / aco / adv / an / asp / b / biz / c / cgl / ck / cm / co / d / diy / e / fa / fit / g / gd / gif / h / hc / his / hm / hr / i / ic / int / jp / k / lgbt / lit / m / mlp / mu / n / news / o / out / p / po / pol / qa / qst / r / r9k / s / s4s / sci / soc / sp / t / tg / toy / trash / trv / tv / u / v / vg / vip /vp / vr / w / wg / wsg / wsr / x / y ] [Search | Home]
If images are not shown try to refresh the page. If you like this website, please disable any AdBlock software!

File: IT exercise.png (9 KB, 997x115) Image search: [iqdb] [SauceNao] [Google]
9 KB, 997x115
Anons, please help! I've got a problem with an exercise for IT. I completed about 95% of the exercise basing on writing different formulas. I stopped on last task wanting me to write a formula, which:

11. Create a formula which will show which city is the coldest and which is the coolest among those showed in table to the left.

I checked a lot of fuctions, formulas, tried to make a picture of possibilities, but nothing helped, unfortunatelly.

What I need to write there to make that formula work? I'm using LibreOffice.

pic related, it's my problem. Table with cities + empty table to the right that must show "the hottest weather will be in..." and "the coldest weathre will be in..." [city]. I need to write a formula that's gonna show city with the hottest and coldest average temperatures.
>>
>>33786
This seems to be similar (although from 2005): https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=78946
>>
>>33793
>https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=78946
Thanks, it helped, although I don't know how to make name of certain city as position, under which the average temperature is placed.
For MIN and MAX it's easy to order to show the highest/the lowest average temperature, but I need to point out the city in which average temperatur is the highest/lowest. Finding cell location is one, but binding certain value to texted cell is another.
>>
for city with highest max temperature try

=INDEX(B21:F21;;SUMPRODUCT(B23:F23=MAX(B23:F23);COLUMN(B23:F23)))

i can't test it right now, but it should work

it's pretty advanced stuff though, i can't image this is for beginners...
>>
>>33816
Woah, thanks, Before I tried this one, I switched cells as for I need the highest average temperatures & some names into Polish, so it looks like this now:

=INDEKS(B21:F21;;SUMA.ILOCZYNĂ“W(B22:F22=max(B23:F23);columns(B22:F22)))

It seems to be no Polish equivalent for COLUMNS in my version of programme. Maybe it's because I'm out of home for a while and I'm on netbook which has OpenOffice installed (it runs faster than LO).

This function doesn't work here, anyway. There's error called "#NAZWA?", like it can't find proper name to describe conditions provided by written function.

Yeah, it is indeed advanced... We should have it in beginning of our University education, not when it's almost over. Same goes with instructions: I wouldn't mind if they added it more, especially when they didn't add any instructions at all to spreadsheet exercises..
>>
>>33819
alright, i at least found Excel, so this works now. i made some errors, so here is the correct version:

=INDEX(B21:F21;;SUMPRODUCT((B23:F23=MAX(B23:F23))*(COLUMN(B23:F23)-COLUMN(A23)))

MAX is the function that finds the highest value
COLUMN is the function that returns the number of the column. COLUMN(A5) = 1, COLUMN(G1) = 7, etc. there must be an equivalent, it's just what you call the vertical progression of the grid / table. in english it's columns (vertical) and rows (horizontal).

so, what the above function does:
build an index of B21:F21 so that B21 is index 1, C21 is index 2, etc. then return the one entry of that index that matches a number.
that number is calculated with sumproduct since you need an array function. first sumproduct finds the the cell with the biggest number (B23:F23=MAX(B23:F23)), then it finds the column number of that cell (COLUMN(B23:F23)) and then subtracts 1 (COLUMN(A23)) so it matches the index of cities (which starts at 1 in column B=2)
>>
>>33828
oh yeah, i just wanted to say: usually when working with numbers, you just use sumproduct, but you can't use it with text output, which is why you need to go through the trouble of making an index.

if you don't like to work with an offset (subtracting 1 from the columns to match the index), just have your index go from A21:F21 since A21 is empty and will never be called anyway. then Katowice is index 2 which matches its column B.

=INDEX(A21:F21;;SUMPRODUCT((B23:F23=MAX(B23:F23))*COLUMN(B23:F23)))
>>
>>33828
>>33831
I tried them (after translation). It didn't work both on OpenOffice and LibreOffice. :/ I'm sure that I translated everything well, even if not, I tried different variations.