Formula Needed - Monthly Budget

Ranger32195

Board Regular
Joined
Feb 4, 2009
Messages
131
Good Afternoon,

I am looking for a formula to sort out a budget problem I am currently working on... I am guessing that the formula is pretty simple but my mind is blank...

What I am trying to do is:

Cell A1 is my cash budget
Cell A3 is actual cash used
Cell A5 is The Difference between A1 & A3 (sum A1-A3)

What I want in Cell A7 is a formula to tell me if my Budget is over 50% either way..

For instance if A1 = 100 and A3 = 155 then I am 55% over and would like a note to appear saying "Explanation required".
If A1 = 100 and A3 = 45 then I am 55% under my Budget and would like a note to appear saying "Explanation required".

Hopefully someone can help me
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Please use the below mentioned formula :

=IF(OR(C3/A3>50%,C3/A3<-50%),"Explanation Required","")

Sanjeev
 
Upvote 0
What I am trying to do is:

Cell A1 is my cash budget
Cell A3 is actual cash used
Cell A5 is The Difference between A1 & A3 (sum A1-A3)

What I want in Cell A7 is a formula to tell me if my Budget is over 50% either way..

For instance if A1 = 100 and A3 = 155 then I am 55% over and would like a note to appear saying "Explanation required".
If A1 = 100 and A3 = 45 then I am 55% under my Budget and would like a note to appear saying "Explanation required".>>>>

In cell A7, the following formula should work. =If(A3>A1*1.50,"Explanation needed Over Budget","OK") -


Your formula for under would be =If(A3<A1*.50,"Explanation needed Under Budget",<A1*.50,"EXPLANATION p Under required Budget)<> "OK")
I seperated each so you could see the principle behind each. You could put one formula in A7 and the other in A8

The formula below combines both elements and can be in A7 using the IF and also the OR statement.

Here is your formula

=IF(OR(A3<A1*0.5, A3>A1*1.5), "Explanation Needed","OK")
 
Last edited:
Upvote 0
Please use this instead of previous one

=IF(OR(A5/A1>50%,A5/A1<-50%),"Explanation Required","")

Sanjeev
 
Upvote 0
Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 122px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">15000</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">1000</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 25px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD>Explanation Needed</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A7</TD><TD>=IF(OR(A3<A1*0.5, A3>A1*1.5), "Explanation Needed","OK")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Here is my formula using this program and it displays correctly finally :)
 
Upvote 0
Good Afternoon,

I am looking for a formula to sort out a budget problem I am currently working on... I am guessing that the formula is pretty simple but my mind is blank...

What I am trying to do is:

Cell A1 is my cash budget
Cell A3 is actual cash used
Cell A5 is The Difference between A1 & A3 (sum A1-A3)

What I want in Cell A7 is a formula to tell me if my Budget is over 50% either way..

For instance if A1 = 100 and A3 = 155 then I am 55% over and would like a note to appear saying "Explanation required".
If A1 = 100 and A3 = 45 then I am 55% under my Budget and would like a note to appear saying "Explanation required".

Hopefully someone can help me


=IF(OR(A5/A1>=50%,A5/A1<=-50%),"Explanation Required","")
SAME AS THE OTHER FORMULA THE MESSAGE WILL JUST POP UP FROM 50% ON THE OTHERS WILL ONLY COME UP FROM 51% ONWARDS
 
Upvote 0
=if(or(a3/a1>=50%,a3/a1<=-50%),"explanation required","OK")

same as the other formula the message will just pop up from 50% on the others will only come up from 51% onwards

also add this formula into cell a6 to calculate the diff between in percent

=(a3-a1)/abs(a1)

hope it helps
 
Upvote 0
My formula keeps displaying wrong - for some reason it wont show when I try to use the less than signs in the statement. Basically it is the same as the others only it used the math of multiplying the budget by either .50 or 1.50 instead of using a %. two different paths to the same answer.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top