confusion with 'greater than >' formula & result

[Neen]

Hi folks, i was wondering if any of you could help me with a little problem i have stumbled across.

I do not venture into the World of excel very often so please be patient as i try to explain

Here is the table i am trying to maintain.
\1

We will focus on Row 3
Column B is basically 550 points + column C = Base Roll.
We need to introduce a cap so no more than 1000 points can be displayed/earned. So column G adds all points & column F caps.
So far it's all ok.
Now when i add a value to column E (points spent) those points are deducted from column G instead of the capped 1000.
Entering a value of 500 displays 800 in column F instead of 500.

The formulas i am using
D

Please Login or Register  to view this content.

F

Please Login or Register  to view this content.

G

Please Login or Register  to view this content.

Any ideas where i am going wrong or any pointers?

Much appreciated

[zbor]

Please rename your title according to the rules:

http://www.excelforum.com/forum-rule...rum-rules.html

Hint: be more specific about your problem, avoid words as help, please etc.

At the end, when you get solution don't foget to make thread [solved]

[mudraker]

Neen

Welcome to Exceltip forum

Please take a couple of minutes and read ALL the Forum Rules then edit your thread title by following the instructions in the rules (Rule 1) so that it better discribes your problem/request

[Neen]

Sorry, i'll have a read & rename etc

You don't often think about reading FAQs and suchm thanks

[Neen]

Title edited, i think it's a bit more clear now :D

[martindwilson]

to cap something use
=min(your sum,1000) now whatever your sum is the most it can be is 1000

[Neen]

Thanks for the reply, this helps me with capping a number.
But i am still having the issue where column F, row 2 displays a value deducted from 1300 instead of 1000.

I hope that make sense

[martindwilson]

you said formula in col f is

=IF(G3 > 1000, "1000",G3)
so where is this value deducted from 1300 fit in with that formula?
cant see that from an image post the workbook

[Neen]

The idea is that column G would add the total points earned (B+C-E), column F would then cap them at 1000.
But when i add a value to column E it doesn't recognise the 1000 cap. So if i add 1000 to the current points spent in the picture i posted, the current points displays 300 when it should be 0.

[martindwilson]

post the workbook!

[Neen]

http://www.mediafire.com/?nmwjmn1gg3n

[shg]

Maybe you could just post it here ...

[Neen]

Sorry i didn't realise i could attach

[shg]

Following Martin's suggestion, in G2 and down, = MIN(1000, 100*B2 + C2 - E2)

[Neen]

Thanks for that, i still seem to be having the same issue though.

I've updated the table a tad with your code. Columns F or G should read 500 though but they read 1000.

A bit more info about this;
This is for a role-playing game, the table has a few hundred names on it.

The idea is that you spend points on anything you wish. But you can not earn over 1000 so the more frequent role players do not leave the less frequent players in the dust so to speak.

So if you earn 20000 points, technically you will never have over 1000, so if you spend 1000 you go to 0 & start again