Need assistance with logical functions, especially "If"

[Joel Abram]

Hi All,

I run a non-profit radio bingo program, and I need help with the spreadsheet so my workers can just input the numbers of sales, and the prizeboard will just be spit out on the spreadsheet. I am having trouble expressing my equations as functions due to my limited knowledge of the excel formulas, especially the logical ones. Below is how i structure the bingo

The cycle starts with a prize of $500 which goes in 48 calls or less, there is also a letter L prize of $100, and a second coverall which starts at $50. 1/3 of sales goes towards the radio and volunteer honourarium, 1/3 goes towards a consolation prize if the progressive jackpot doesn't go, and 1/3 makes the next jackpot bigger. to complicate matters the progressive number increases by one every bingo. the 1st progressive jackpot is capped at $3000, any over flow from the 1/3rd sales goes into the second coverall which is capped at $2000. If there is a bingo on the full card in the progressive number or less the progressive jackpot is given out. If not the consolation prize is given out (1/3rd of sales). below is an example

say we have the first bingo in the cycle at 48 numbers and we have $3000 in sales, the prize board would be as follows

progressive jackpot = $1500 (initial $500 plus 1/3 sales which is only given out if someone gets a full card in 48 calls or less

consolation prize = $1000 (1/3 sales and only given out if noone gets a full card in 48 calls or less)

letter L = $100

2nd coverall=$50 (this number increases by $50 for every thousand the progressive jackpot increases starting at $2000, eg. if progressive jackpot is $3500, then 2nd cover is $150)

say noone gets the first full card in 48 numbers or less

the actual prizes given out are consolation of 1000, letter l of 100, and 2nd cover of 50
and the next prizeboard is progressive number is 49, progressive jackpot is $1500 + 1/3 of sales, consolation s 1/3rd of sales, etc.

Another element is that the max the 1st progressive jackpot can get is $3000, any overflow would go on the second coverall which maxes out at $2000, and any overflow there goes to start the next cycle. i will illustrate below

say now the progressive number is 55 the prizeboard could typically look as below

if sales = $6000 and previous progressive jackpot = $2800

then progressive jackpot = $3000 (previous jackpot of $2800 +1/3 of sales (2000) but maxes out at $3000 so $1800 goes to 2nd coverall)
2nd coverall = $1950 (consolation 2nd coverall of $150 plus $1800 as mentioned above, and this only goes if the 1st progressive jackpot goes in 55 numbers or less)
letter L=$100

I hope someone can make sense of what i am trying to do in terms of a spreadsheet. I have some of the basics completed, but man it is complicated. thanks if anyone can help me out here.

[FDibbins]

it might be easier to understand what you want, and offer some suggestions, if you uploaded a sample workbook. make sure you show what you have, some examples of what your expected outcome is, and how you arrived at that

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.