Microsoft Excel

Herbers Excel/VBA-Archiv

Bandbreitenberechnung mit Ampelfunktion

Betrifft: Bandbreitenberechnung mit Ampelfunktion von: Marius
Geschrieben am: 22.09.2014 15:11:41

Hallo
Ich möchte im Kennzahlenreporting mit MIN und MAX Bandbreiten arbeiten und diese mit der Ampelfunktion verknüpfen. Beispiel bei der Kennzahl EBITDA:

Ist Wert 2013 = 6,5 Mio

MIN Wert = 5,0 Mio.
MAX Wert = 7,0 Mio.

Folgende Regel müsste für die Ampelfunktion gelten:
1) Wenn sich der Wert in der Bandbreite, oder im Beispiel EBIDTA darüber, befindet sollte die Ampel grün sein.
2) Wenn sich der Wert unter der Bandbreite befindet sollte die Ampel rot sein.
3) Die Ampel sollte Gelb anzeigen, wenn sich der Wert innerhalb einer 20%-Grenze, ausgehende von der Differenz, befindet. Für das Beispiel würde diese heissen: 7,0 Mio minus 5,0 Mio = Differenz von 2,0 Mio. Davon 20% ergibt 0,4 Mio. Würde sich der Wert also zwischen 5,0 und 5,4 Mio bewegen, müsste die Ampel auf Gelb schalten. Bewegt sich der Wert dann zwischen 5,4 und 7,0 Mio oder mehr, würde die Ampel Grün anzeigen...

Ich habe bis dato noch keine Formel gefunden, die dies abbilden könnte....

Vielen Dank
Marius

  

Betrifft: AW: Bandbreitenberechnung mit Ampelfunktion von: Jack_d
Geschrieben am: 22.09.2014 16:00:56

Hallo MArius

ich würde das so (über bedingte Formatierung) lösen.

Wobei ich nicht verstehe warum einmal die Werte über dem Max grün sind, und dann die Werte innerhalb des Spektrums auch grün sind. Daher hab ich 2 verschiedenen Grün´s genommen.

Grüße



Tabelle1

 ABCDE
1Min5   
2Max7   
3IST6,5   
4     
5     
6  Ampel6,5 
7     
Bedingte Formatierung wird in dieser Tabelle nicht dargestellt

verwendete Formeln
Zelle Formel Bereich R1C1 für Add In
D6=B3  =R[-3]C[-2]

Bedingte Formatierung Haupttabelle 1  
Zelle   Nr. Bed.  Regeltyp  Operator   Formel1   Formel2   Format Schrift Füllfarbe   Unterstrichen   Schrift- farbe Muster   Musterfarbe Typ
D601.Bed.: Zellwert istzwischen=($B$2-$B$1)*0,2+$B$1 =$B$25296274         1
D602.Bed.: Zellwert istzwischen=$B$1 =($B$2-$B$1)*0,2+$B$165535         1
D603.Bed.: Zellwert istgrößer=$B$2  5287936         1
D604.Bed.: Zellwert istkleiner=$B$1  255         1
Bedingte Formatierung Haupttabelle 2 oberere/unterer Bereich    
Zelle   Nr. Bed.  Format Zelle Anhalten Auswahl   Anzeige   Anzahl   Durchschnitt   Typ  
D601.Bed.   Falsch          1
D602.Bed.   Falsch          1
D603.Bed.   Falsch          1
D604.Bed.   Falsch          1
Die Bedingungen wurden mit Excel-Version ab 2007 ausgelesen.


http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip
http://hajo-excel.de/tools.htm
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 14.15 einschl 64 Bit



  

Betrifft: AW: Bandbreitenberechnung mit Ampelfunktion von: Marius
Geschrieben am: 22.09.2014 16:38:10

Hallo Jack_d
Perfekt - die beiden Grün sind eine sehr gute Idee. Funktioniert bestens. Vielen herzlichen Dank
Marius


  

Betrifft: Vielen Dank für die Rückmeldung von: Jack_D
Geschrieben am: 22.09.2014 18:04:45

Hallo Marius

das freut mich. Viel Spaß weiterhin.
Und danke für die Rückmeldung.

Viele Grüße