Hi there,
Prob1; (tab1 spreadsheet)
Trying to make a spreadsheet to keep track of financial numbers in different businesses.
Put an example chart here. Trying to make this formula so that when you come into 2019 and enter income day by day the formula will update when the 1st million is made, 2nd etc.
Prob2; (tab2 spreadsheet)
And i also have a problem with circleref. in a % formula i made that also should come into action as the year goes.
Any help would be much appreciated!
Hi, and welcome to the forum.
Can you explain in a narrative form what you are trying to achieve. I can't help thinking it would be better if your data in A1:H31 was kep in a normalised table of dates and values, i.e. a two column List
Analysis is then much simplfied.
You have a circular ref because the formulae in column F are referriing to themselves. What are you trying to show. The Default or 3rd term of an IF formula is usually the value you want if the first part isn't true. In your formula in F4 you're simply saying that F4 for is zero.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Hi,
I dont really have any excel background, so what i get from what you are saying is more or less; I don't setup my excel spreadsheets the right way for formulas etc. to work properly.
If you could take a look i can send the main sheet for you to take a look at and give me feedback on the setup. (Which i think might be incorrect to make use of functions).
Last edited by saaHarstad; 12-05-2018 at 04:49 PM.
Hi again,
Filling in some context, if you havn't seen scheme 1. (I would strongly suggest to take a glance at it before picking up example sheet 2)
The formula im missing is mainly a summary of equal the one i request in example scheet on tab 1, if this is not possible to make with the collumn/rows setup the way i have in Example sheet 2 here (larger area of nr's vs example sheet) how can it be done to make a formula that counts and tells the amount of days spent to accumulate 1 million in income automatically?
The 2nd problem is that the formula shown on example sheet on tab 2 goes to circle ref. But when it gets numbers during 2019 it will automatically transform into a % in a positive or negative value. (Does it even matter??) Or should i just put false part of =if equation to a static cell with text "TBC" ?
Like said before, any help will be greatly appreciated, been banging my head against Youtube tutorials the last couple of days to try and figure out a formula or any other way to accumulate this formula up ahead of the numbers actually appearing under 2019.
The point of this formula is to be able to compare how many days you use to generate 1 million in income compared to other years, as in 2017 maybe it's 30 days vs 2018 you use 40 days etc. To keep track off key points in total income.
Last edited by saaHarstad; 12-05-2018 at 08:54 PM.
This proposal for Prob1, from file attached to post #1, employs four helper columns which may be moved and/or hidden for aesthetic purposes.
Column J is populated using:Formula:
Please Login or Register to view this content.
Column K is populated using: =IF(J2="","",INDEX(J$2:J$300,AGGREGATE(15,6,(ROW($2:$300)-1)/(J$2:J$300<>0),ROW(1:1))))
Column L is populated using: =IF(K2="","",MOD(SUM(K$2:K2),1000000))
Column M is populated using: =IF(OR(L3>L2,K3=""),"",ROW()-1)
The 'Number of days' is displayed using:Formula:Please Login or Register to view this content.
The 'Average per day' is displayed using: =IF(B38="","",1000000/B38)
For Prob2 the circular references may be removed by using:
D4: =IF(E4>1,((E4/C4)-100%),0)
F4: =IF(G4>1,((G4/E4)-100%),0)
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks