Forum Discussion
If formula
Alternative
= LET( s, BYROW(data, LAMBDA(d, MAX(SUM(d), 0))), VSTACK(s, SUM(s)) )
Yousef70 You still have not explained what the result should be and why. The last formula is quite odd and can be shortened to:
=MAX(0,O6:O36,SUM(O6:O36))
Dear Riny ...
sorry for not explaining to you what I am trying to do ( I will try to do my best )
I am doing excel accounting calendar for payments to suppliers ( I am a retailer ) it is like a provision for what should be paid according to date , thus , I have 30 to 31 cells vertical .
if payment is honored it is positive , if a payment is not honored it is negative ( I still need to see it not honored to schedule the payment to a later date but not accounted for in the result )
the needed result is to know total actual paid daily and monthly
I have done that =MAX(0,O6:O36,SUM(O6:O36)) but the result is only the highest positive cell not a sum of positive cells
- Dear Hans...
Noted Forums such as this one, Microsoft Community and others are intended to help people on their way, not to create complete solutions for them.
If you make a start yourself and run into a specific problem, you are very welcome to post a question about it here. But if you want a complete solution, you should hire a professional developer.
- Dear Riny
I understand , can you direct me where to go ?
Thanks Yousef70 Sorry, but that kind of work goes beyond the scope of my involvement in this forum
Dear Riny...
Kindly , looking for your help in developing attached excel sheet further , I will elaborate on what is needed :-
First , I have started this sheet many years ago with two parts in it ( it is as a daily excel calendar ) :-
- Upper part is for my business ( calculating break even point on profit margin provision )
- Lower part is personal ( income distribution based on % of each expense to total expenses )
Second , profit margin provision is entered manually at day one of each month based on last month average
Third , weekends take half the daily cost ( sales are usually lower on weekends ) where weekdays take full cost + difference from weekends
Fourth , total sales needed to break even with cost is calculated based upon profit margin which is divided to weekdays and weekends accordingly
Fifth, I need to automate few things in this sheet :-
- Need to have profit margin average automated on daily basses ( when data is entered ) . daily profit is at line 11 starts at cell T
- Need to have all cells with profit margin update to daily average accordingly , for example cells C37 , J20 , K20 , etc.
- Lost sales to break even point starts at line 12 cell T ( based on provision ) , need a formula at cell K20 to add lost sales daily after dividing it to remaining weekdays , same at cell P20 for weekends ( keeping in mind profit margin average ) , Also , if sales exceed the break even point , the formula will subtract from remaining weekdays for cell K20 and weekends for cell P20.
This it for know , we will fine tune accordingly
Thanks in advance
- THANKS A MILLION FOR YOUR INPUT
IT SEEMS THAT MY NEEDS ARE COMPLICATED Yousef70 I don't see anything like "honored", "not honored" or "not due". I have no clue what you are talking about. Sorry.
Dear Riny …
Attached excel accounting calendar that I managed to create according to my knowledge
please , open August sheet look at columns M ( checks ) and N ( taxes ) both are post dated "not due" yet , once Due , it is either "honored" it will in positive numbers or "not honored" it will be in negative numbers where your formula will work to only add positive numbers but not "not due" cells as per not determined yet whether "honored" or "not honored".
thanks in advance
Yousef70 Sorry, don't understand. Can you upload/share a file clearly showing your intentions?
- Dear Riny ...
this one =SUMIF(M6:N6,">=0",M6:N6) Yousef70 Add to which formula?
Dear Riny …
Kindly , can you add to above formula a "not due status" to give to result cell a zero total till date is today as per I have started August provision and interred all post dated checks amounts .
Thanks in advance- Dear Hans ...
Many thanks for your help
I found that the status sheet easier to use The attached workbook contains two sheets.
The first sheet has manually colored amounts. It uses a custom VBA function SumGreenPositive to sum the positive values in green.
Disadvantage: if you change the font color of an amount, Excel will not automatically update the result of the formula. You'll have to wait until Excel recalculates, or press F9.
And you'll have to allow macros for it to work. Macros don't work at all in Excel Online and in Excel for Android and iPhone/iPad.
The second sheet has an extra column with the status.
The amounts in column A have not been colored manually, but by using two conditional formatting rules.
The positive "Honored" values are summed by an ordinary SUMIFS formula.
Advantage: if you change the status of an amount, the result of the formula will be updated automatically.
Dear Hans …
thanks for your input
what I meant in color conditioning is to enter a number in green , red & black color to effect the result cell input for example :-
1- If I enter a number in green , to accounted for in result cell ( calculated in total )
2- If I enter a number in red , not to be accounted for in result cell ( not calculated in total )
3- If I enter a number in black , not to be accounted for in result cell ( not calculated in total )
If you have a sample , please , share it with me
Thanks in advanceI'd add a column with the status as text.
You can then use SUMIF or SUMIFS to add amounts with a specific status, and use conditional formatting to color the amounts.
Yousef70 Use that expression as you please. It's not mine 🙂
VBA = Visual Basic for Applications. In short, a programming language used in Excel to automate all sorts of processes that otherwise can't be achieved by regular formulas.
- Dear Riny …
I understand you my friend , I like your expression " not my cup of tea " may I use it
What is VBA though ? Yousef70 Excel isn't very good at calculating based on colors. That would require VBA which is not my cup of tea.
Dear Riny …
Hooray, it did work perfectly , thanks a million
one more question :
can the result be conditioned to font color ??!! if possible for example :
1- payment honored , green font , accounted for
2- payment not honored , red font , not accounted for
3- payment not yet due , black font , not accounted for
Yousef70 Then you should consider using SUMIF like in the example in the picture below. This will sum only positive amounts.
