Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 28th 12, 11:11 AM
Junior Member
 
First recorded activity by ExcelBanter: Jan 2012
Posts: 1
Default Caculating the remainding amount

Hi everyone.

First time poster here, but i have read through alot of threads and i cant seem to find an answer that can help me, so i thought i join up and become an active member

Ill cut straight to the point

I am at an intermediate level with excel, and i was wondering if my following problem can be solved, ill give a basic run-down, and if anymore detail is required please let me know

After a few caculations etc etc i have 2 cells left that i need to get the following to happen

I will call them A and B

A has an amount which could be negative or positive.
B always starts with 3000 (could be 4000 or anything else later on, but for all purposes 3000 is fine)

Is there a way so that if A is ever a negative, i can have an amount taken from B to bring A back to zero.

If A is positive, nothing would be done, but if A was -345.32 for example, that exact amount is taken from B

That is the most direct description i can give, but if more info is reequired just let me know, because there possibly might be a way around this with another column or two to bypass this

And help would be great

Thanks in advance

  #2   Report Post  
Old January 28th 12, 07:20 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2011
Posts: 829
Default Caculating the remainding amount

"bob__" wrote:
First time poster here

[....]
i have 2 cells left that i need to get
I will call them A and B


So for your edification as a newbie here, it is better to call them what
they are (e.g. A1 and B1).

And it is prudent to show exactly the formulas or constant that each cell
contains in order to minimize ambiguities that delay your getting a usable
answer.

Bob wrote:
B always starts with 3000 (could be 4000 or anything
else later on, but for all purposes 3000 is fine)

[....]
If A is positive, nothing would be done, but if A
was -345.32 for example, that exact amount is taken
from B


Perhaps the following in B1:

=MIN(3000,3000+A1)

or

=MAX(0,MIN(3000,3000+A1))

The difference depends on whether or not you want to allow B1 to go negative
(A1 < - 3000).

  #3   Report Post  
Old January 28th 12, 10:33 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 524
Default Caculating the remainding amount

On Sat, 28 Jan 2012 11:11:21 +0000, bob__ wrote:

A has an amount which could be negative or positive.
B always starts with 3000 (could be 4000 or anything else later on, but
for all purposes 3000 is fine)

Is there a way so that if A is ever a negative, i can have an amount
taken from B to bring A back to zero.

If A is positive, nothing would be done, but if A was -345.32 for
example, that exact amount is taken from B


In B1:

=3000+min(0,A1)

If A1 is negative, min(0,A1) is A1, and that negative amount will be
added to B1, or in other words B1 will be reduced.

If A1 is positive (or 0), min(0,A1) is 0, an dB1 will be 3000.

--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Caculating multiple percents in a worksheet Paula New Users to Excel 4 January 16th 08 08:14 AM
Workbook not caculating formulas RodShaw2 Excel Discussion (Misc queries) 3 May 28th 07 07:20 PM
Caculating Columns Between Certain Dates Dermot New Users to Excel 19 January 11th 06 04:26 PM
Caculating a "staircase" discount Thomas Refsdal Excel Worksheet Functions 3 August 24th 05 08:33 AM
I need help Caculating Zip code Distances Dougal[_2_] Excel Programming 1 July 15th 05 05:12 PM


All times are GMT +1. The time now is 01:49 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017