BuzzerBeater Forums

Hi! I really love the job you did on this tool - it looks and works GREAT. The only problem i think i found so far in version 1.0.2 is that:

Sheet TrainerParrot, the 3 tables in training comparison (+Logscore, +Salary and Rel. Cost) seem to have wrong formulas in them, in all meaningful fields of these tables I see ###, which is an Excel exception (I use Excel 2007). For example, let's take Training group 1 for Logscore table.

The cell I32 (training group 1) has the following formula in it: =IF($A32<>I$30;"";SUMPRODUCT(OFFSET($U$10;0;ROW()-ROW($A$32)):OFFSET($U$27;0;ROW()-ROW($A$32));I$65:I$82)), which is populated into every cell of this row (only the numbres of cells and rows changed respectively where allowed) and into the rest of the rows of this table.
The cell F33 (training group 2) has the following formula in it: =IF($A33<>B$30;"";SUMMPRODUCT(OFFSET($U$10;0;ROW()-ROW($A$32)):OFFSET($U$27;0;ROW()-ROW($A$32));B$65:B$82)), so even as we are calculating values for the training group 2, we take values from column U, where there are values set by manager for training group 1.

So I suggest, that correct formulas should look like:
=IF($A32<>I$30;"";SUMPRODUCT(OFFSET($U$10;0;ROW()-ROW($A32)):OFFSET($U$27;0;ROW()-ROW($A32));I$65:I$82))
and
=IF($A33<>B$30;"";SUMMPRODUCT(OFFSET($V$10;0;ROW()-ROW($A33)):OFFSET($V$27;0;ROW()-ROW($A33));B$65:B$82))
First we should change the letters of columns for respective training type and remove char "$" before the number from ROW() functions for correct populating into below. I corrected my spreadsheet according to that and everything works just fine. Though probably, i just downloaded incorrect version and it is completely my misunderstanding.

Hope what i wrote above makes sence - i'm not a native english speaker. If you find this comment useful, please BB-Mail me so i could probably explain more clearly what i mean.

And again - thanks for the great job you did on this tool, it really rocks!!!

OK, good thing that it's not a common problem and that you are aware of it - that was the reason I posted the message, just in case :)

Thanks for the way of fixing it (didn't work in my case though - I guess some Excell problem or something), but I've fixed the problem for myself anyway and it works just fine - really helpful and supportive tool. Maybe someone else will find our last messages useful. :)

Thanks again for the great tool. Cheers. :)

For those who are completely unaware of how to properly copy+paste formulas (like me 10mins ago lol), simply copy the formula in b32 through clipboard>copy and paste the formula to b32:ae41 using clipboard>paste special>formulas. everything should work now.

rinse and repeat for b42 and b52. i hope this guides clueless excel users :)

actually you can repair the Excel version manually, but I didn't dare describing this work-around publicly because it will frighten many people off: 1.) go to the cell b32; 2.) change any number in the formula and press Enter; 3.) change the number back to the original value and press Enter; now it works; 4.) copy the formula in b32 to all cells of the range b32:ae41 - then repeat steps 1-4 for b42 and b52.

The problem is related to the matrix formulas used. In excel you have to press ctrl+shift+enter so that the formula has the { } at begin and end and then excel can finally recognize it as a matrix.

I just finished toying with your tool and it seems really good. I'm now playing a bit with the training sheet.. if I understand everything correctly the program calculates the increase in performance (called logscore) depending on the tactics played (distribution entered in my data) of every single pop in a given skill.
What I don't understand, for example, is why your tool thinks that every one of my trainees need lots of JR. Or why it prefers a training which gives me a couple more pops but a much more unbalanced and with an higher wage player

The problem is related to the matrix formulas used.

these are not matrix formulas. it is a conversion error that corrupts the file.

Sumproduct is the sum between the products of the elements of a matrix, at least in my excel file.
If you correct a cell with ctrl+shift+enter, than copy that cell and paste special (formulas) everything is then fine, at least in my file

I'm going to give your training a try, it is pretty close to what I had in mind (with some more JS from my point of view, indeed).

I'm having trouble cutting and pasting from the Set Lineup page. It either puts it in one cell or seperates the name into two columns. Any suggestions?

I use IE. The normal paste was splitting the name and I don't know why. I might just try Firefox and doing a paste special since I do have it on my computer. Thanks.