Bonus calculator in Calc

users
#1

A while back I posted up about calculating bonuses based upon a lookup. now, I need to add an additional step. Every once in a while, agents get additional bonuses for sales outside of the normal commission ladder. Here's the layout of the main sheet

A B C D E
                                                         Date1 Date2 Date3 .....
agent ID Agent Name $### $### $###

What I wanted to do was add an "Additional Bonus sheet" With a layout like this

A B C
agentID Date Bonus

Then have a "final bonus screen" with this layout and formula similar to the main sheet

A B C D E
Date1 Date2 Date3
agentID Agent Name $(sales + add bonus total) ... ....

Note: the agent can have multiple bonuses for the same date on the additional bonus sheet. Where would i start doing something like that? I might figure that out while I'm typing that out (which is probably one of the most useful functions of this list), but right now I'm spinning.

#2

I know it's a relational database. :wink: Is there any way to design this to work on a spreadsheet?

#3

Let's assume I can't get IT to install the JRE for database access.

#4

Hi :slight_smile:
Why not use Base to read the 2 existing sheets? Then let it do the
bit it does best?
Regards from
Tom :slight_smile:

#5

Hi :slight_smile:
That is very much like a relational database. The 1st sheet is the
main table. The 2nd sheet would be a separate table but would be
linked by a relationship between both column As. The 3rd sheet would
actually not be a table as such, it would be a Query that takes data
from the 2 tables and performs calculations on them. Hmm, it could be
a Form or Report but if i was doing it (and note that i'm not) then i
tend to prefer using a Query for as much of this sort of thing as
possible because it makes it easier to adjust the formulae in the
future
Regards from
Tom :slight_smile: