Excel: Display Profit Plus or Minus Per Company

How do you display for each company which one had a total positive profit (revenue minus costs) and which one a negative. My idea:

Here are the data from A1:D11:

	Revenue	Costs	Profit
Company 1	€ 500	€ 400	€ 100
Company 2	€ 1.000	€ 760	€ 240
Company 3	€ 890	€ 900	€ -10
Company 4	€ 640	€ 700	€ -60
Company 5	€ 570	€ 600	€ -30
Company 6	€ 875	€ 800	€ 75
Company 7	€ 690	€ 600	€ 90
Company 8	€ 500	€ 600	€ -100
Company 9	€ 1.050	€ 700	€ 350
Company 10	€ 900	€ 700	€ 200

From F1:H11 I created formulas:

Revenue - Costs Plus	Revenue - Costs Plus	Revenue - Costs Minus
=IF(D2:D11>0;B2:B11;NA())	=IF(D2:D11<=0;B2:B11;NA())	=IF(D2:D11<=0;C2:C11*-1;NA())
=IF(D3:D12>0;B3:B12;NA())	=IF(D3:D12<=0;B3:B12;NA())	=IF(D3:D12<=0;C3:C12*-1;NA())
=IF(D4:D13>0;B4:B13;NA())	=IF(D4:D13<=0;B4:B13;NA())	=IF(D4:D13<=0;C4:C13*-1;NA())
=IF(D5:D14>0;B5:B14;NA())	=IF(D5:D14<=0;B5:B14;NA())	=IF(D5:D14<=0;C5:C14*-1;NA())
=IF(D6:D15>0;B6:B15;NA())	=IF(D6:D15<=0;B6:B15;NA())	=IF(D6:D15<=0;C6:C15*-1;NA())
=IF(D7:D16>0;B7:B16;NA())	=IF(D7:D16<=0;B7:B16;NA())	=IF(D7:D16<=0;C7:C16*-1;NA())
=IF(D8:D17>0;B8:B17;NA())	=IF(D8:D17<=0;B8:B17;NA())	=IF(D8:D17<=0;C8:C17*-1;NA())
=IF(D9:D18>0;B9:B18;NA())	=IF(D9:D18<=0;B9:B18;NA())	=IF(D9:D18<=0;C9:C18*-1;NA())
=IF(D10:D19>0;B10:B19;NA())	=IF(D10:D19<=0;B10:B19;NA())	=IF(D10:D19<=0;C10:C19*-1;NA())
=IF(D11:D20>0;B11:B20;NA())	=IF(D11:D20<=0;B11:B20;NA())	=IF(D11:D20<=0;C11:C20*-1;NA())

and from J1:K11:

Revenue - Costs Minus	Profit Plus	Profit Minus
=IF(D2:D11>0;C2:C11*-1;NA())	=IF(D2:D11>0;D2:D11;0)	=IF(D2:D11<=0;D2:D11;0)
=IF(D3:D12>0;C3:C12*-1;NA())	=IF(D3:D12>0;D3:D12;0)	=IF(D3:D12<=0;D3:D12;0)
=IF(D4:D13>0;C4:C13*-1;NA())	=IF(D4:D13>0;D4:D13;0)	=IF(D4:D13<=0;D4:D13;0)
=IF(D5:D14>0;C5:C14*-1;NA())	=IF(D5:D14>0;D5:D14;0)	=IF(D5:D14<=0;D5:D14;0)
=IF(D6:D15>0;C6:C15*-1;NA())	=IF(D6:D15>0;D6:D15;0)	=IF(D6:D15<=0;D6:D15;0)
=IF(D7:D16>0;C7:C16*-1;NA())	=IF(D7:D16>0;D7:D16;0)	=IF(D7:D16<=0;D7:D16;0)
=IF(D8:D17>0;C8:C17*-1;NA())	=IF(D8:D17>0;D8:D17;0)	=IF(D8:D17<=0;D8:D17;0)
=IF(D9:D18>0;C9:C18*-1;NA())	=IF(D9:D18>0;D9:D18;0)	=IF(D9:D18<=0;D9:D18;0)
=IF(D10:D19>0;C10:C19*-1;NA())	=IF(D10:D19>0;D10:D19;0)	=IF(D10:D19<=0;D10:D19;0)
=IF(D11:D20>0;C11:C20*-1;NA())	=IF(D11:D20>0;D11:D20;0)	=IF(D11:D20<=0;D11:D20;0)

When you want, you can replace all the formulas by names.

Then I created a stacked bar graph based on F1:I11.
Changed the colors
Chose categories in reverse order.
Y-axis labels to Low.
Maximum for X-axis to 1200 and minimum to 1000.
Number format of the X-axis to

#.##0;#.##0

Then added J1:K11.
Added both to the secondary axis.
Again maximum for X-axis to 1200 and minimum to 1000.
Added the secondary Y-axis.
Again categories in reverse order.
Deleted the secondary X-axis
Deleted the secondary Y-axis.
Added data labels.
Formatted them with:

_ € * #.##0_ ;_ € * #.##0_ ;_ € * "-"??_ ;_ @_

Added the graph title using the formula:

=Blad1!$A$37

In A37 the formula:

=Blad1!$B$1 & " Minus " & Blad1!$C$1 &": " &Blad1!$D$1

You can also download the file revenueplusminus.zip through this link:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing

Friesland: nu ruim 1500 Friese familiewapens doorzoekbaar

Inleiding Het totale overzicht is nu ook als PDF te downloaden: rptWapens Al jaren verzamel ik de teksten van grafzerken en andere voorwerpen om ze in een database bijeen te brengen. Op heel veel oude grafzerken, rouwborden of andere voorwerpen in Friesland treffen we naast tekst ook vaak familiewapens aan. Ruim een jaar geleden ben ik gestart met het koppelen van familiewapens aan deze teksten. Met behulp van deze database kunnen we nu steeds meer voorheen onbekende wapens thuisbrengen of andersom, initialen herleiden tot de bijbehorende namen. Een voorbeeld. Zo kennen we uit het boek Fries Zilver nummer 894 met de tekst: TDH RBB 1669 Tjitte D. Wijngaarden geboren 6 october 1874 Daarbij een mannen- en een vrouwenwapen. Mannenwapen blijkt dat van Hoitinga te zijn. De letters blijken vervolgens te herleiden tot Tjepke Douwes Hoitinga en Rienkje Bouwes Bruinsma uit Nijland. Vermoedelijk getrouwd in 1669! Opgelost. Ander voorbeeld. Op een lepel uit het boek Dokk...

Meer lezen

Excel: VBA script om wachtwoord te verwijderen

Af en toe krijg ik een vraag om een wachtwoord van een Excel blad te halen. Doodsimpel met VBA. Hier een script dat ik gebruik: Sub WachtwoordCrack() Dim a As Integer, b As Integer, c As Integer, d As Integer, _ e As Integer, f As Integer, g As Integer, h As Integer, _ I As Integer, j As Integer, k, m As Integer Dim begin As Date, eind As Date Dim duur As String Dim objSheet As Worksheet begin = TimeValue(Time) On Error Resume Next For Each objSheet In Application.Worksheets For a = 65 To 66: For b = 65 To 66: For c = 65 To 66 For d = 65 To 66: For e = 65 To 66: For f = 65 To 66 For g = 65 To 66: For h = 65 To 66: For I = 65 To 66 For j = 65 To 66: For k = 65 To...

Meer lezen

Excel: Creating Your Own Tabs With XML And VBA

In order to create you own nice looking tabs you need the Custom UI Editor for Microsoft Office. You can download it for free. How is this different from choosing File , Options , Customize Ribbon and adding a new tab with new groups and then commands to the group? You can also move the new tab to any position. T hen your new tab is always there. Even when you don't open the specific file with the right VBA. In my example the outcome looks like this, a nice tab, placed in front of the Home Tab. You will only get this tab when you start the specific example (which you can download). The XML behind this: <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> <ribbon startFromScratch="false"> <tabs> <tab id="MaxIlze" label="Dossier Overzicht" insertBeforeMso="TabHome"> <group id="customGroup1" label="Adressen verrijken"> ...

Meer lezen

Hessel de Walle's Blog

Zoeken in deze blog