negative positive formulas

Hi, I need help figuring out how to program a formula.

I will have one cell displaying the monthly budget, another cell displaying 
the actual amount spent.

now, when subtracted, i'd like to have one cell that only displays the value 
if we are under budget, and another cell that only displays the value if we 
are over budget. is there a formula to do this? or visual basic?

please let me know if you can help!

thanks

Em :o)

 
0
emmy128 (2)
11/24/2004 9:25:02 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
528 Views

Similar Articles

[PageSpeed] 48

emmy128 wrote:
> Hi, I need help figuring out how to program a formula.
>
> I will have one cell displaying the monthly budget, another cell
> displaying the actual amount spent.
>
> now, when subtracted, i'd like to have one cell that only displays
> the value if we are under budget, and another cell that only displays
> the value if we are over budget. is there a formula to do this? or
> visual basic?
>
> please let me know if you can help!
>
> thanks
>
> Em :o)

Say A1 is the budget and B1 is the expenditure
In C1 enter =IF(A1>B1,A1-B1,"") and in D1 enter =IF(B1>A1,A1-B1,"")
C1 will show the value if under budget, and D1 if over budget.

HTH



0
GordonBP1 (262)
11/24/2004 9:28:54 PM
one way:

A2:     <budget>
B2:     <actual>
C2:     =IF(B2<=A2, B2, "")
D2:     =IF(B2<A2,"", B2)

Where C2 will display the actual value if you're under (or on) budget 
and D2 will display the actual value if you're over budget.

In article <65FA0719-ED94-4C1A-8D2C-4226318AD61D@microsoft.com>,
 "emmy128" <emmy128@discussions.microsoft.com> wrote:

> Hi, I need help figuring out how to program a formula.
> 
> I will have one cell displaying the monthly budget, another cell displaying 
> the actual amount spent.
> 
> now, when subtracted, i'd like to have one cell that only displays the value 
> if we are under budget, and another cell that only displays the value if we 
> are over budget. is there a formula to do this? or visual basic?
> 
> please let me know if you can help!
0
jemcgimpsey (6723)
11/24/2004 9:35:21 PM
If your data is in A1 and B1 then in B3 use this =IF(A1-B1>0,A1-B1,"") in B4
use this =IF(B1-A1<0,B1-A1,"").

Steve


"emmy128" <emmy128@discussions.microsoft.com> wrote in message
news:65FA0719-ED94-4C1A-8D2C-4226318AD61D@microsoft.com...
> Hi, I need help figuring out how to program a formula.
>
> I will have one cell displaying the monthly budget, another cell
displaying
> the actual amount spent.
>
> now, when subtracted, i'd like to have one cell that only displays the
value
> if we are under budget, and another cell that only displays the value if
we
> are over budget. is there a formula to do this? or visual basic?
>
> please let me know if you can help!
>
> thanks
>
> Em :o)
>
>


0
steveh2789 (23)
11/24/2004 9:35:30 PM
Let's say the final 'Actual Spent' amount, (positive or negative) wind
up in column C. 

In Column D we display the value if <= Budget:
=If(C1>=0,C1,"")

In Column E we can display the value if over budget (where C < 0)

=If(C1<0,C1,"")

Of course, instead of the actual spent amount being in column c you ca
include its calculation in the above formulas

--
crispb
-----------------------------------------------------------------------
crispbd's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1088
View this thread: http://www.excelforum.com/showthread.php?threadid=32026

0
11/24/2004 9:37:42 PM
Sorry I meant in C1 use this =IF(A1-B1>0,A1-B1,"")  and in D1 use this
=IF(A1-B1<0,A1-B1,"")

Steve


"Steve H." <steveh@mrinstitute.org> wrote in message
news:uatsr1m0EHA.1524@TK2MSFTNGP09.phx.gbl...
> If your data is in A1 and B1 then in B3 use this =IF(A1-B1>0,A1-B1,"") in
B4
> use this =IF(B1-A1<0,B1-A1,"").
>
> Steve
>
>
> "emmy128" <emmy128@discussions.microsoft.com> wrote in message
> news:65FA0719-ED94-4C1A-8D2C-4226318AD61D@microsoft.com...
> > Hi, I need help figuring out how to program a formula.
> >
> > I will have one cell displaying the monthly budget, another cell
> displaying
> > the actual amount spent.
> >
> > now, when subtracted, i'd like to have one cell that only displays the
> value
> > if we are under budget, and another cell that only displays the value if
> we
> > are over budget. is there a formula to do this? or visual basic?
> >
> > please let me know if you can help!
> >
> > thanks
> >
> > Em :o)
> >
> >
>
>


0
steveh2789 (23)
11/24/2004 9:42:05 PM
Emmy,

This could easily be done with a couple "if" statements.  Let's say that 
your budget amount is in cell A1 and your actual amount spent in B1 and then 
underbudget cell is C1 and overbudget cell is D1.  Then in C1 (for 
underbudget) you would put an if statement that says: =IF(A1-B1>=0,A1-B1,"") 
which says that if the budget amount minus the amount spent is greater than 
or equal to zero then show that amount, otherwise, show nothing.  In cell D1 
you would put a similar formula that says: =IF(A1-B1<0,A1-B1,"").  Now this 
will show a nebative number in the cell if you are overbudget.  If you don't 
want that (i.e. to show overbudget as a pos #) then modify the equation to 
say:  =IF(A1-B1<0,B1-A1,"").  One last thing, if you are trying to add up the 
columns later, sometimes the "" part might give you problems depending on 
what formlas you use (probably not, but just in case) if so, just replace the 
"" with zero and then you could do some conditional formtting on the cells to 
hide the zero if you don't want it to show.

"emmy128" wrote:

> Hi, I need help figuring out how to program a formula.
> 
> I will have one cell displaying the monthly budget, another cell displaying 
> the actual amount spent.
> 
> now, when subtracted, i'd like to have one cell that only displays the value 
> if we are under budget, and another cell that only displays the value if we 
> are over budget. is there a formula to do this? or visual basic?
> 
> please let me know if you can help!
> 
> thanks
> 
> Em :o)
> 
>  
0
Brandt (32)
11/24/2004 9:49:10 PM
Thanks, that worked perfectly!!

Em :o)

"Gordon" wrote:

> emmy128 wrote:
> > Hi, I need help figuring out how to program a formula.
> >
> > I will have one cell displaying the monthly budget, another cell
> > displaying the actual amount spent.
> >
> > now, when subtracted, i'd like to have one cell that only displays
> > the value if we are under budget, and another cell that only displays
> > the value if we are over budget. is there a formula to do this? or
> > visual basic?
> >
> > please let me know if you can help!
> >
> > thanks
> >
> > Em :o)
> 
> Say A1 is the budget and B1 is the expenditure
> In C1 enter =IF(A1>B1,A1-B1,"") and in D1 enter =IF(B1>A1,A1-B1,"")
> C1 will show the value if under budget, and D1 if over budget.
> 
> HTH
> 
> 
> 
> 
0
emmy128 (2)
11/24/2004 10:55:02 PM
Reply:

Similar Artilces:

problem: formula appears as text
Hello...I'm stumped.... I am entering a simple formula: =RIGHT(A2,2) in a cell. The sheet has data on it but not a whole lot going on. I have tried formatting the cell itself, the column, and the entire sheet a number of ways...however, the formula appears in the cell as =RIGHT(A2,2)...as if were a text entry...it won't actually just turn into a formula and do it's thing. Thanks in advance for response.... -- couriced ------------------------------------------------------------------------ couriced's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=...

Text in formula?
What a pain in the neck to come back home after being away and not being able to access the newsgroups via my newsreader (http:// www.gmayor.com/MSNews.htm). Anyway ... <g> I have this formula in cell A16: ="Don't take today: " &+A15+1 I got it from googling for text and formulas in same cell. I've tried formatting cell as general and text but nothing comes out right. The result in A16 should say: Don't take today: Tue.Sep.14.2010 but instead it says: Don't take today: 40435 Can anyone advise how to fix this? Thanks! :oD On Mon, 30 Aug 2010 ...

problem with formula #2
I have one column with increasing standard time (hr:min:sec). I put a formula in the first cell of the first row of a second column to convert the same cell of the first column to a value of decimel based upon minutes. The result is correct in the first cell of the first line (row) where the the formula was placed in the second column, but when I copy the formula throughout the rows of the second column, the results shown are always the answer for the first line - even though when I klick on the cell the formula in the lower rows/cells shows it referring to the correct later time ...

How can formulas be used to create another formula?
This is probaby very simple, but I really don't know a lot aout formulars. When I have created formulas to work out the percentage of each sales type that we have, I then need to use this % as a part of another formula, Ie Sales type A is 50% of our overall sales "=sum(B1/B3)*100", I am am then trying use this 50% to apportion our wages bill to a sales type. When I create another formula "=sum(B10/B13)*the cell reference of the formula I previously calculated" it is throwing out the incorrect answer. Can anyone give me a simple answer to a very simple question a...

Lotus 123 print, formula, format, ect
How can I get Excel to emulate Lotus 123 commands such as: print range, + - (formula) range, move information and calulatons. ...

How to apply a formula accross all the rows when the total rows are too much in no. ?
Hi, guyz i know you can drag the formula accross the rows in that column t have that formula in effect in every cell. but i have some data lik 5000 rows then how to get that formula accross each cell, its painfu holding the mouse and dragging the formula accross the rows. please advise thanks in advance sorab -- Message posted from http://www.ExcelForum.com Hi after inserting this formula in the first row double click on the lower right corner of your cell selection -- Regards Frank Kabel Frankfurt, Germany > Hi, > guyz i know you can drag the formula accross the rows in that col...

Does Excel support mixed formatting of a string in a formula cell, yet?
Excel is one of the most spectacular apps ever created. But one feature I really need is mixed formatting of a string whose cell is a formula. By mixed formatting I mean, you know, making selected (as opposed to all) character(s) of the string bold, italic, a different color, sub- or superscripted, etc. In Excel 2002 you cannot do that in a formula cell (right?). In edit mode, the user would, say, press F9 to display the calc'ed string, selectively format it as usual, then cancel edit. Excel would save the formatting as a separate mask. What could be easier? The user would ...

Formulas #43
I have a cell with a simple currency formula in it. I want to add a specific dollar amount to the formula result IF a single value from a group of 5 values appears in another cell. -- Thank you. John Q. Without more details, maybe something like: =A1+IF(OR(B1={"a","b","c","d","e"}),22,0) John Q wrote: > > I have a cell with a simple currency formula in it. I want to add a specific > dollar amount to the formula result IF a single value from a group of 5 > values appears in another cell. > -- > Thank you. John Q. -...

thousand separator as indian style for negative number
hi every body we in india to any number put comma as stated below ie 15,13,10,565.00 fifteen crores thirteen lacs ten thousand five hundred sixty five only 00,00,00,000.00 but excel we have only thousand separator.it separates the number afte every thousand. can it possible in excel to formate number as per our style. 00,00,00,000.00 for both + and - numbers. if possible please guide me kamlaka -- kamlaka ----------------------------------------------------------------------- kamlakar's Profile: http://www.msusenet.com/member.php?userid=328 View this thread: http://www.ms...

Scorekeeping Formula
Does anyone have a formula set up scorekeeping in Excel for the game Rummikub. We have tournaments and want to use the computer to keep track of scores, with minus scores added to the winners' scores. Thanks DocT ...

need help with Index, Match and Countif in the same complicated formula
Hi, I have a spreadsheet Expense Journal designed for entering expenses in two currencies, one for Dollar and one for a local currency. It has two sections of rows, the top is for Dollar Expenses and uses no exchange figures because the spreadsheet converts everything to Dollars. This Dollar section has five columns: Date Project # Account # Description Dollar Amount In the blank spreadsheet that they start anew each quarter, there are only two rows setup for Dollar expenses because most of their expenses will be in Local currency. Below the Dollar secti...

in formula a range cell reference eg) $A1:$A20 that is static when filling
if i want a cell reference to not increment when filling i put a dollar sign in front of it i noticed this does not work when you specify a range of cells eg ) $A1:$A20 although it is not an error when i fill in a formula it still increments the cell reference in each row but if i specify a single cell it does not. does anyone know the syntax for this, it's hard to word so i havent been able to find anything on google. thanks for your help! cheers, /sh You mean like this: =SUM($A$1:$A$20) ? -- HTH, RD --------------------------------------------------------------------------- P...

Formula for counting cells with value 'x' provided Col U = 'y'
Hi guys, Need some help with a formula please. Have tried myself and know that the answer is straightforward but going through a mental block! Col Q Col R A01 58 A01 62 P02 62 Both columns data starts in cell 14 and runs down to cell 10000. I need to count the entries in Column Q that = A01 ONLY IF Col R = 62. Then do the same for Q = A01 ONLY IF R = 58, and so on. The worksheet is a year-to-date file and will be updated weekly with many entries in both columns with varying values. Many thanks. Hi, =SUMPRODUCT((Q1:Q10000="A01")*(R1:R1000...

Formulas to run offline in 3.0?
Hi CRM Pros I have a prospective 3.0 customer. Basically they have a not too complex Excel sheet, that they would like to replace with CRM functionalty instead, in order to run it offline with automatic sync to the CRM server. I know that I can customize CRM 3.0 with extra fields on the oppertunity entity and with custom exchange rate and freight rate tables. But what are the possibilities in 3.0 of creating calculations to run automatically, e.g., tallying the sum of some of the custom fields, or for multiplying some of them together with the exhange rate from a separate table? These calcula...

Count formulas
I am trying to do a set of 3 counts between 1-100. Under 80 Between 80-90 and 90+. I have 2 formulas that are working and can't seem to figure the 3rd, could anyone help? 1st =COUNTIF(G4:K22,">89.99") 2nd =COUNTIF (G4:K22,"<79.99") It is the middle one that I am not able to figure out. count all -those two or =sumproduct((g4:k22>=80)*(f4:k22<90)) -- Don Guillett SalesAid Software donaldb@281.com "Barb" <anonymous@discussions.microsoft.com> wrote in message news:045801c3d85f$f193bdb0$a401280a@phx.gbl... > I am trying to do a set of...

Formula?
Hi, I have my data set up the following way - the columns refer to th month and the rows are the different projects I'm tracking. Projec "A" has data in months Jan-Oct (10 values), while Project "B" has dat in months Jan, March, July, August & Sept (5 values)-the missing month are blank. Is there a way get the average of the last 4 values fo each project? Maybe using a count function? thank -- Message posted from http://www.ExcelForum.com Hi if your values are in A1:J1 use the following array formula (entered with CTRL+SHIFT+ENTER) =AVERAGE(OFFSET(J1,0,0,1,-...

formula #59
i am trying to do this:: =IF(J48<25,"",K48) just insert the info from I48 into K48 BUT =IF(J48>25) then add J48 info to I48 and place the total in K48. is this possible and how is it done =if(j48<25,"",if(j48>25,j48+i48)) -- y_not ------------------------------------------------------------------------ y_not's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19947 View this thread: http://www.excelforum.com/showthread.php?threadid=394739 A formula can only enter values into the cell in which it resides, it cannot enter data int...

Appending or Up-dating a formula
I am using an =Ave function in a formula to average quite a few cell values together. I add new information all the time; sometimes 5 or 6 new cells at a time. How can I append or up-date my formula to include these new cells with out have to type the new cells into the formula? When I make the cell that has the formula in it active/edit, all of the cells that are referenced in the formula have a highlight around them. Is there a key or key combination I can press while clicking on the cells I want to add to the formula? Thanks, john Hi see your post in Excel.misc -- Regards Frank ...

if and formula
hi i am trying to check if a time value is greater that 2am and smaller than 2.30 am if it is put 1:59:30 in the cell if it the time value is smaller or bigger put cel value into cell i am using the formula below =IF(AND(C73>="02:00:00",C73<="02:30:00"),"01:59:30",C73) i am checking the formula with 02:01:00 (should put 1:59:30 ) actuall puts 02:01:00 i am checking the formula with 01:01:29(should put 01:01:29) actuall puts 01:01:29 but i can't get it working correctly any ideas thanks kevin By enclosing those times within quotes you are conve...

Duplicate a chart with relative formulas
If I have several sheets, each with data for a particular year, how can I copy a chart from 1 year to all the others, making sure the data references are to the respective year? Duplicate the sheet that has the chart, then replace the data on the copied sheet. - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ On 3/23/2010 9:29 PM, Bobhoe wrote: > If I have several sheets, each with data for a particular year, how can I > copy a chart from 1 year to all the others, making sure the data references > are to the respective year? ...

IF Formula 01-13-10
I am trying to compare two cells to eachother and populate a third cell with the number that is the GREATER of the two. (IF f26 is greater than G34, populate m22 with value in f26; BUT if g34 is greater than f26, populate m22 with value in g34). Any suggestions? On Jan 13, 10:17=A0am, CrazyConfused <CrazyConfu...@discussions.microsoft.com> wrote: > I am trying to compare two cells to eachother and populate a third cell w= ith > the number that is the GREATER of the two. =A0(IF f26 is greater than G34= , > populate m22 with value in f26; BUT if g34 is greater than...

orden en los operadores de las formulas
deseo saber cual es el orden correcto para generar formulas en una hoja de excel -- Excel "angies" <angiemarbella27@hotmail.com> wrote in message news:E03784BA-F3E8-4E54-84EA-00D151DD939A@microsoft.com... > deseo saber cual es el orden correcto para generar formulas en una hoja de > excel > -- > Excel La multiplicación y la división antes de la adición y de la substracción. Por ejemplo, si usted escribe =5+2*3, el resultado será 11. Si quiere 21 por el resultado , tiene que usar parentesis: =(5+2)*3 ...

FOrmula to work out pricing rounding up and down
I have a formula in cell that reads "=3DIF(C7>0,"FOC",-C7*1.175)" it turns a negative into a Positive and a Positive FIgure into answer of "FOC" Sometimes it will display a result of =A32 or =A35.50 or whatever the figure when the original answer is a negative. With me so far!! The result which is displayed as a number i need it to round up t the nearest 9.99. For example if c7 is =A34.00 display answer of 9.99 if c7 is =A311.00 display answer of 19.99 if c7 is =A328.43 display answer of 29.99 So no matter what the answer is it rounds up to the 9.99 answer...

format a timesheet it adds the hrs but need a negative for flex
I have been working on a timesheet for work I've got the format to add the hrs for the day and the total for the week but when the overtime adds on to the flex it want give me a negative it just comes up with ######## can you please help thanks One way to show negative dates/times in excel is to use the 1904 date system. Tools|Options|Calculation tab Be aware that any dates in your worksheet will change by 4 years and a day. But if you're adding hours, how do you get a negative value? juls kartinyeri wrote: > > I have been working on a timesheet for work I've got the...

Solving a formula
I have the following formula that I need Excel to solve for different values of P6 ( I have different values of P6 (P7, P8, P9 (reference cells)). I need the value of x for the different values of P6. Is this possible? It seems like it should be but I can't find out how to make Excel do this. Please help! Thanks. 70000 = P6*((((x/80)*(1/5280))*(2/3))+(((x/40)*(1/5280))*(1/3))) If I am not mistaken, x equals: = 22176000000 / P6 HTH -- Dana DeLouis Win XP & Office 2003 "Anth" <Anth@discussions.microsoft.com> wrote in message news:6196C6C5-FC82-42E2-B6A1-B2...