Pergunte aqui

Perfil de steveTu - atividade

2019-03-01 15:40:23 +0200 Medalha recebida  Pergunta popular (fonte)
2019-03-01 11:08:51 +0200 Marcada como a melhor resposta Averageif and Weekday

Good morning, In calc, I have a couple of columns - one with dates and the other with values. What I want to do is to average the values where the date column equates to a weekend and similarly for a weekday. So something like AVERAGEIF($A$1:$A$10,WEEKDAY(?,2)>5,$B$1:$B$10)

...but how can I get WEEKDAY to work on the cell from the range? Or is there a better way of going about it?

Thanks

2019-03-01 11:08:47 +0200 Respondeu uma pergunta Averageif and Weekday

That's grand - thank you. I had a problem with shift-ctrl-enter (Linux) - it didn't make it an array function - but ther

2019-03-01 09:29:13 +0200 Perguntou Averageif and Weekday

Averageif and Weekday Good morning, In calc, I have a couple of columns - one with dates and the other with values. What

2017-08-08 21:08:24 +0200 Medalha recebida  Pergunta famosa (fonte)
2017-07-27 14:53:58 +0200 Medalha recebida  Pergunta famosa (fonte)
2017-04-20 06:43:22 +0200 Medalha recebida  Pergunta famosa (fonte)
2017-02-23 00:32:13 +0200 Medalha recebida  Pergunta notável (fonte)
2017-02-22 14:13:29 +0200 Medalha recebida  Professor (fonte)
2017-02-22 14:13:29 +0200 Medalha recebida  Autodidata (fonte)
2017-02-22 13:43:26 +0200 Medalha recebida  Editor (fonte)
2017-02-22 13:41:44 +0200 Medalha recebida  Pergunta popular (fonte)
2017-02-22 13:40:32 +0200 Respondeu uma pergunta Find max cells in a range

For anyone else with a similar issue, I have created a macro to scan a range of cells (controlled by entries on a control sheet in the calc spreadsheet) and to find the first, second and third (including ties) by value - and to convert the first, second and third to a name in a heading row.

I'm not a macro writer (this is my first attempt) - so apologies if the code is a bit naff and there are shorter ways of doing this:

REM  *****  BASIC  *****

SUB Main
DIM doc         AS OBJECT
DIM sheets      AS OBJECT
DIM currSheet   AS OBJECT

' The control sheet - which columns/rows to look at
DIM ctrlSheet   AS INTEGER
DIM ctrlRow     AS INTEGER

' Control fields - which rows AND columns will be traversed
DIM startRow    AS INTEGER
DIM startCol    AS INTEGER
DIM noRows      AS INTEGER
DIM noCols      AS INTEGER
DIM headRow     AS INTEGER
DIM targetCol   AS INTEGER

' Any cell, any row, any column, any cell value
DIM cell        AS OBJECT
DIM col         AS INTEGER
DIM row         AS INTEGER
DIM value       AS INTEGER

' Max array - 0 is first, 1 is 2nd AND 2 is third
DIM maxValue(3) AS INTEGER

' String Array - FOR 1st, 2nd etc converted to heading strings....
DIM maxHead(3)  AS String

doc         = ThisComponent
sheets      = doc.Sheets

' Get the start row AND start columns AND number of rows AND columns....
ctrlSheet   = sheets.count() - 1
ctrlRow     = 0
'DEBUG print ctrlsheet

' In this case I have three blocks that I need to eavluate...the three blocks relate to three photo competitions -
' the first competition is based on the months (so 12 rows AND 6 competitors), 12 random phrases, AND a wild card (a single row)
DO WHILE 1=1
    ' Set the current sheet to the control sheet
    currSheet   = doc.Sheets(ctrlSheet)
    ctrlRow     = ctrlRow + 1

    ' EXIT the loop IF no more control blocks........So stop when first startRow is empty
    cell = currSheet.getCellByPosition(0,ctrlRow)
    IF cell.type = com.sun.star.table.CellContentType.EMPTY THEN
        EXIT DO
    END IF  

    ' Get the control fields - which rows AND columns to check AND which is the heading row to convert the max's to strings
    startRow    = cellString(currSheet,0,ctrlRow)
    startCol    = cellString(currSheet,1,ctrlRow)
    noRows      = cellString(currSheet,2,ctrlRow)
    noCols      = cellString(currSheet,3,ctrlRow)
    headRow     = cellString(currSheet,4,ctrlRow)
    targetCol   = cellString(currSheet,5,ctrlRow)
    'DEBUG print startRow,Startcol,noRows,NoCols,headRow


    ' OK - so we have the basic control columns - now loop over the data ranges AND get 1st, 2nd AND 3rd values...
    ' The issue is that we can THEN have 'joint' values for 1st, 2nd, 3rd - so once we know the values that are first, 2nd etc
    ' THEN loop back over the cells in the row to find that value AND build string from their heading row values
    currSheet = doc.sheets(ctrlSheet-1)

    FOR row = startRow TO startRow+noRows-1
        maxValue(0)     = 0
        maxValue(1)     = 0
        maxvalue(2)     = 0
        FOR col = startCol TO startCol+noCols-1

            'cell = doc.sheets(0).getCellByPosition(col,row)
            'value = cell.getValue()
            value       = cellString(currSheet,col,row)

            IF value ...
(more)
2017-02-20 15:51:48 +0200 Medalha recebida  Pergunta notável (fonte)
2017-02-20 14:50:15 +0200 Medalha recebida  Acadêmico (fonte)
2017-02-20 12:47:26 +0200 Medalha recebida  Pergunta popular (fonte)
2017-02-20 12:32:52 +0200 Medalha recebida  Estudante (fonte)
2017-02-13 18:48:02 +0200 Perguntou Autoformat - writer/calc - missing something?

I have set up a calendar in writer that uses a table for the body of the calendar. Good so far. I can then format the table to fix the row size, assign background colours, borders etc... Again fine. I have also then set up styles for each month (a page style) and for 'red letter days' and previous/next month's days. What I then wanted to do was to set up a style for the table itself, so that the days are automatically given the same background colour, font, font effect etc - then making it very simple to change the appearance of the calendar. I thought that 'Autoformat' would then do that for me, bit I seem to be having a problem. I set up a table with 7 columns, starting with Monday and finishing on the right with Sat/Sun. The heading row is given a background colour of Grey 10%, and the body of the table a background of light green, with the two weekend columns being given backgrounds of light blue. Again all fine so far. However, if I click on a cell in the table and then do a Table-Autoformat-Add and specify my name for the format (Calendar), it save it, but changes the format. The header colour and main body colours are both preserved ok, but instead of saving the two weekend columns as a separately coloured block, it only saves one column. Also the row height does not appear to be preserved.

Is there something that I'm missing or does autoformat only cater for top row, first column, last column, bottom row type settings? And is there a way of preserving the set row height/column widths as well? All the check boxes in the autoformat creation screen are checked.

2017-02-10 10:09:39 +0200 Resposta comentada Writer ...or... for a calendar

..the page format per month worked fine - but I had a bit of an issue with the table used for the calendar body. In its properties it had links to the page, so copying it around initially caused me issues as it tried to copy the page format as well. Removing those and associated links from the table properties resolved that issue. The 'working' test calendar is a bit too big to upload here (40MB+)...but if anyone wants a copy, contact me.

2017-02-09 11:27:50 +0200 Resposta comentada Writer ...or... for a calendar

...once I get a working version, I'll post the document here (if I'm allowed) in case someone else wants to do the same thing...

2017-02-09 11:26:58 +0200 Resposta comentada Writer ...or... for a calendar

OK - I think I know how to do it. I want a simple three area set up, so a typical writer page seems to be the best way - as Mike said. The problem is that the header and footer is common across pages - so where I want to use the header for an image per month, that couldn't work. But it can if I define separate page formats for each month - so a january Page format, Feb Page format etc. That way each page can have its own header image and footer that says the month. This appears to work ok...

2017-02-08 18:14:17 +0200 Respondeu uma pergunta Writer ...or... for a calendar

Mike, I initially used header and footer and the page body as the three way split, but I thought that if I wanted different images for each page in the header then that would not work, so I went the table route! Is there a 'container' that I'm missing in one of the tools that allows me to configure a page into 3 sections? I just want to be able to fix the page layout. Or is that simply a style that I can configure? Given that I have a different image per page, a different set of dates and a different month name, I think I just want a fixed layout and to then be able to copy a section from one month to the other.

2017-02-08 17:03:42 +0200 Medalha recebida  Pergunta notável (fonte)
2017-02-08 10:03:38 +0200 Medalha recebida  Entusiasta
2017-02-08 05:58:08 +0200 Medalha recebida  Pergunta popular (fonte)
2017-02-07 19:21:06 +0200 Perguntou Writer ...or... for a calendar

I want to set up a calendar for this year. Simple. What is the easiest way to define a 3 area page layout that I can quickly copy and modify from page to page? I want three areas - the top area being a graphic (photo) that is to be the same size on each page - the middle dates area and a final area indication the month. I tried using writer and set up a 'table in table' type layout, with a single column, 3 row table as the base. Top row for the image, and a second sub table in the middle row, and a text box in the final row. The problem with that was when I inserted the image into the top row, it adjusted the size of the row. I faffed around and then got a slightly cropped version of the image, but that then lacked a background - so I put a rectangle of a semi transparent colour in the top row and brought the image up to the top. That gave me a relatively ok first month, but now I want to copy it all around. I thought 'select all' would then allow me to copy/paste the whole layout to page (month) 2, but no joy. I would guess that there's an easier way of doing this? - any pointers appreciated.

2017-02-03 11:18:40 +0200 Perguntou Find max cells in a range

I have a spreadsheet of 'scores' by user and I want to know which user has the max score. Users can 'tie' - ie have the same score, so I want to select the 'tied' users.

I am using: =IF(MAX(B8:M8)=0,"No Winner",CONCATENATE(INDEX($B$4:$M$4,MATCH(LARGE(B8:M8,1),B8:M8,0)),",",INDEX($B$4:$M$4,MATCH(LARGE(B8:M8,2),B8:M8,0))))

BUT that has problems as it just repeats the same user. I think I want to be able to adjust the range in the second check - ie if the 'winner' is I8 to then start the search for the tied account over the range I8+1:M8 - but I can't see how to do that. Can someone give me a pointer?

Thanks, Steve