How can I use Calc to identify changes to commission each month

[SueM1]

I’ll be importing two columns of spreadsheet, one which is client names and changes slightly month to month, and the second column which is commission received from each client, and I need to make sure the numbers stay with the respective client.

IE: Mr Bloggs $16.72 Mr James $33.19 Fox & Fox $20.21

And the next month it could be: Mr Bloggs 20.89 Mr James $30.33 Mrs New $5.91

I want to be able to clearly see if the amount relating to the respective client is significantly different from the past month (ie > or <$10 difference - indicating a possible issue or error that would need to be followed up on). I was worried about trying to make a formula that wouldn’t be able to take into account if a new person was in the column, and mess up the numbers etc. Any thoughts would be welcome!

[Lupp]

May I understand this as follows:
You vave a complete list of clients in (say) column A. Every month you import two additional columns (say D and E) containing a client name left and a related amount right of it in the same row. You now want to have in columns B and C the imported client names (for a check) and the respective amounts aligned with the same client names in column A. How shall the previous contents of B and C be kept for the comparison?

[librebel]

Hello Sue,

just add a column for “Month”, and a column for “Year”, so that you now have 4 columns, called “Month”, “Year”, “Client”, “Received”.

Then you fill in the rows, e.g. Row 2:

5 2017 “Mr Bloggs” $16.72

then Row 3:

5 2017 “Mr James” $33.19

etcetera for the whole month of May (5), then for next month June you can just add a new Row below the existing Rows, e.g.:

6 2017 “Mr Bloggs” $20.89

etcetera…

[librebel]

Hello Sue,

just add a column for “Month”, and a column for “Year”, so that you now have 4 columns, called “Month”, “Year”, “Client”, “Received”.

Then you fill in the rows, e.g. Row 2:

5 2017 “Mr Bloggs” $16.72

then Row 3:

5 2017 “Mr James” $33.19

etcetera for the whole month of May (5), then for next month June you can just add a new Row below the existing Rows, e.g.:

6 2017 “Mr Bloggs” $20.89

etcetera…

[SueM1]

but then i need it to compare the two months by customer amount, how can i do that?

[Ratslinger]

I am certainly no expert in Calc but I believe you can do this with VLOOKUP. Columns and compared amounts will vary depending upon requirements.

Given last months names are in ‘F11-F22’ and last months amounts are in ‘G11-G22’. Then current Months names in ‘Bxx’ with current amount in ‘Cxx’. This formula in ‘Dxx’:

=IF(VLOOKUP(B12,F11:G22,2,0)<C12-C12*0.1,"HIGH",IF(VLOOKUP(B12,F11:G22,2,0)>C12+C12*0.1,"LOW","OK"))

would display “#N/A” if the name was not found, “OK” if the amount was within +/- 10%, "LOW’ if the amount was more than 10% less than last months figures, and “HIGH” if the amount was more than 10% higher than last months figures.

Then for next month, copy this months names/figures into ‘F11-G22’ and the new months names/figures into B & C column with the lookup formula remaining in column D. Of course, the example uses 10% as the variation. You would change this to fit your needs.

[SueM1]

I hope that works! I will give it a try when I get home! Thanks

[SueM1]

they all just come up as N/A I think i’ll have to play with it a bit. I thought having both months set of data in the same column with the month as the first column, (so columns would be: Month/Client/Commission), then using the filter drop-down and sort by client name first is an interesting outcome. Thirdly, a query?

[Ratslinger]

Make sure you understand how VLOOKUP works and how to set the fields correctly. Sorting is not necessary because of the last parameter - 0.

[SueM1]

I think it’s worked! Sorry, I was using excel before as I was on another computer. now just tried in calc and it looks like it’s worked, with various low, high and n/a. Thanks so much!!

[Ratslinger]

@SueM1 I am glad you have a result which is acceptable. However, don’t discount the answer from @Lupp. There are some extremely valid points depending upon your situation.

[SueM1]

I don’t think it’s worked after all anyway. And I’m not sure what @Lupp is saying. I only want to compare the current month to the previous one. If all the client names stayed the same that would be easy, But sometimes new clients are added (so no previous month to compare with, want them off the list) and sometimes current clients leave (so won’t be in current month, and want them off the list too).

[Ratslinger]

Not sure what you are getting at. With the given formula, if a client is new this month, “#N/A” will be returned since it is not in the previous months list. If a client is in the previous months list but not in the current month, nothing happens because it was never accessed from the current month.

[Lupp]

I don’t feel sure to understand what you actually want to achieve in detail. In specific I did not yet understand for how many months you want to be able to compare the amounts assigned to any customer. I cannot take the “two months” for granted. In what way do you intend to dispose of the old data gotten “out of sight”? You will not only have to import new pairs of columns but also to get rid of old ones. Any “new” month will be an “old” one a month later.

Another very important issue is that you have to assure the distinctness of the customers containe in any of your given or imported column. Otherwise: If a customer can occur more than once in a single list you have to specify precisely what kind of handling you need. You will neither find a second occurrence by standartd means nor be able to assign more than one new pair to the lead list.

On the track @Ratslinger started, I made this demo that should contain every kind of formula you might need (in the “distinctness-assured-case”). It’s an example for your studies, not a ready-made solution.