formule vraag met meerdere malen als - dan , als - dan

[sanderc]

beste forum helpers,

ik zit met een vraag om een formule te maken voor het volgende.

ik heb een excel bestand met onder elkaar alle gebouwen van gemeente X.
in de kolommen daarnaast staan bepaalde gebruiksfuncties, in deze kolom kan dus per bouwwerk aangegeven worden welke funties in het gebouw aanwezig zijn doormiddel van een kruisje te zetten in de juiste kolom(men). nu is er ook een kolom waar in staat hoeveel rookmelders er aanwezig zijn in het totale bouwwerk. nu is het zo dat er een berekening uitgevoerd moet worden met deze rookmelderaantallen. maar deze berekening is per gebouw functie anders. dus nu ben ik op zoek naar de formule zoals hieronder in woorden beschreven.

als je een x tegen komt in kolom 1 t/m 4 dan doe je cel a1 / 100 en dan de bijbehorende berekening maar als geen x tegenkomt in deze kolommen maar wel in kolom 5 t/m 8 dan voer je berekening 2 uit en als je daar geen x tegenkomt maar wel in kolom 9 t/m 12 dan voer je berekening 3 uit.

hopelijk heb ik hem zo een beetje duidelijk omschreven.

wie kan me helpen met zo'n formule of het begin ervan

ik hoor graag van jullie en alvast bedankt.

 

[popipipo]

Een klein xls voorbeeld bestandje (zonder gevoelige informatie) zegt meer dan 221 woorden

 

[sanderc]

hier is dan een voorbeeldje van het bestand.

het is dus de bedoeling dat met bijv. het getal in cel B2 een berekening uitgevoerd wordt. het antwoord komt in cel I2. en de kruisjes bepalen welke berekening er gedaan wordt. het eerste kruisje telt.

hopelijk is het nu nog iets duidelijker.Bekijk bijlage voorbeeld.xls

 

[Cobbe]

Doet deze formule in I2 hetgeen je voor ogen hebt?

[B]=ALS(AANTAL.ALS(C2:H2;"x")>0;B2/100;"")[/B]

Cobbe

 

[oeldere]

even een begin gemaakt (zie bijlage)

=ALS(OF(C2="x";D2="x";E2="x";F2="x");B2/100;" ")

is dit wat je bedoelt.

 

[sanderc]

Ja inderdaad,

jullie zijn beide op de goede weg. alleen nu is het nog zo dat er dus een verschillende berekening uitgevoerd moet worden. ik heb in de nieuwe bijlage duidelijk proberen te maken wat er moet gebeuren.

het is eigenlijk zo dat het systeem van links naar rechts moet kijken. er zijn dan klusters van kolommen met dezelfde berekening. als er in het eerste kluster niks staat dan gaat hij verder naar rechts voor het tweede kluster maar daar hoord een nieuwe berekening bij. hopleijk maak ik het niet te moeilijk (voor mezelf ook)

wederom alvast bedankt voor jullie reactieBekijk bijlage voorbeeld.xls

 

[Cobbe]

Deze uitleg voldoet niet, hoe kom je bv aan D13 en E13 in je formule?
Hoe wordt dat bepaald?
Kun je in je bestand niet gewoon schrijven wat de bedoeling is, en waar welke resultaten moeten komen?

Cobbe

 

[sanderc]

ja je hebt gelijk het wordt er niet duidelijker op. heb nog een keer het bestandje aangepast nu met enige uitleg erbij. hopelijk is dit dan een goede verduidelijking.

alvast bedankt

Bekijk bijlage voorbeeld.xls

 

[Cobbe]

Volledig naast de .... gevallen.

Ben bezig aan de oplossing.

Cobbe

 

[sanderc]

hahahahahaha ja prachtig.

ik denk inderdaad dat hij de juiste waarde geeft. maar ik kan nu helemaal niet meer volgen wat ie doet. en ik krijg deze berekening ook niet meer aangepast aan het bestaande document. de vorige opzet kan ik nog volgen en was ook al zover dat ie naar de tweede kolom kluster keek alleen hij telde de antwoorden op of gaf als waarde WAAR of ONWAAR ipv getallen. op onderstaande manier gaf hij de som dat was niet mijn bedoeling.

pfoe wat een ingewikkeld ding

kan het niet met onderstaande formule? er zijn maar 4 klusters dus ben op de helft.
=ALS(OF(C2="x";D2="x");B2/100*(D13+E13);" ")*ALS(OF(E2="x";F2="x");B2/100*(D12+E12);" ")

 

[zapatr]

Zie bijlage.
De oplossing geldt voor de gegevens die NU in je werkblad staan en het is daarop dat ik mij heb gebaseerd. Aangezien ik niet weet hoe eventuele nieuwe gegevens er uitzien, heb ik andere mogelijkheden niet in ogenschouw kunnen nemen. Maar alles is natuurlijk altijd aan te passen...

 

[sanderc]

Dankje wel

hij doet inderdaad precies wat ik bedoel. maar kun je hem misschien ook iets toelichten zodat ik hem aan kan passen aan de juiste gegevens. ik denk dat ik hem voor het grootste gedeelte begrijp. een vraag is bijvoorbeeld: het lijstje met rekenwaardes staat rechts langs de tabel werkt het dan nog?

als je me kunt verklaren welke getallen verwijzen naar welke cellen dan kom ik er denk ik wel uit.

mijn dank is groot

sander

 

[zapatr]

als je me kunt verklaren welke getallen verwijzen naar welke cellen dan kom ik er denk ik wel uit.

Wat functies precies doen, staat erg goed uitgelegd in de helpfile van Excel, dus daar wil ik je toch vooral (ook) naar verwijzen. Een goede kennis van functies is onontbeerlijk Om Excel redelijk onder de knie te krijgen (mensen horen dat vaak niet graag, maar het is wel zo). Met mijn toelichting hieronder erbij, moet het je dan wel duidelijk zijn. Zoals je zult ziet, vormt jouw probleem een heel gepuzzel (voor mij toch).
Hierboven schreef ik al dat bovenstaande oplossing niet geldt voor ALLE mogelijke situaties. Om een universele oplossing te maken moeten AL die MOGELIJKE situaties bekend zijn. Zo ben ik er bv. van uitgegaan dat de factor C1 altijd gecombineerd wordt C2, en B1 met B2. Of dat in werkelijkheid ook zo is, dat blijkt niet uit je vraag.

Herschrijving van je berekening:
=(B2/100*C1)+(B2/100*C2)
=B2*0,01*(C1+C2)
=B2*0,01*(D13+E13)

Formule in i2 :
=B2*0,01*(INDIRECT("d"&KIEZEN(VERGELIJKEN("x";$C2:$H2;0);13;13;12;12;13;13))+
INDIRECT("e"&KIEZEN(VERGELIJKEN("x";$C2:$H2;0);13;13;12;12;13;13)))

=VERGELIJKEN("x";$C2:$H2;0)
In rij 2 (C2:H2) wordt gekeken in welke kolom de eerste x voorkomt.
Het moet een de EERSTE x zijn en het moet een EXACTE overeenkomst zijn, vandaar de 0 op het eind. (Een exacte overeenkomst is het automatisch, want iets anders dan x staat er niet in die rij). Dat levert voor gebouw 1 een 1 op. En die 1 is de eerste functie die in de risicoklasse voorkomt. We zien daar ook dat bij de Woonfunctie de berekeningen C1 en C2 behoren.

=KIEZEN(VERGELIJKEN(....);13;13;12;12;13;13)
Omdat VERGELIJKEN een 1 opleverde, wordt dit: =KIEZEN(1;13;13;12;12;13;13), d.w.z.:
kies uit de reeks die na de 1 komt het eerste getal: dat is 13.
13;13;12;12;13;13 zijn de rijnummers in de risicoklasse die bij resp. de woon(C)-, industrie-(C), winkel(B)-, zorg-(B), cel(C)-, en kantoorfunctie(C) horen.

=INDIRECT("D")&KIEZEN(VERGELIJKEN)(....) wordt nu:
=INDIRECT("D"&13) = INDIRECT(D13) = 1,05

Het tweede gedeelte van de formule (na de +) doet hetzelfde voor C2 (E13), wat 0,45 als resultaat geeft. We krijgen dan:
= B2*0,01*(C1+C2)
=2,1*(D13+E13)
= 2,1*(1,05+0,45)
=2,1*1,50 = 3,15

het lijstje met rekenwaardes staat rechts langs de tabel werkt het dan nog?

Ja, mits je in de formule de verwijzingen naar de tabel aanpast.

 

[sanderc]

ik had je al hartelijk bedankt. want ik was er ondertussen helemaal uitgekomen. maar nu heb ik toch nog 1 vraagje.

is het mogelijk om als er nog geen waarde berekend is of de waarde onduidelijk is dat deze verborgen wordt?

dus dat de tekst ''#N/B'' of ''#Waarde!'' verborgen wordt?

ik hoor graag van je. hieronder heb ik de formule gekopieerd zoals ie moet zijn in mijn document.

=K3*0,01*(INDIRECT("AB"&KIEZEN(VERGELIJKEN("x";$L3:$W3;0);5;4;5;5;5;4;3;5;3;4;4))+INDIRECT("AC"&KIEZEN(VERGELIJKEN("x";$L3:$W3;0);5;4;5;5;5;4;3;5;3;4;4)))

nogmaals bedankt ben al bijna bij het einddoel!

 

[zapatr]

ik was er ondertussen helemaal uitgekomen

Jammer dat je dat niet eerder liet weten, want bovenstaande uitleg kostte mij anderhalf uur.
(en de oplossing eergisteren een aantal uren).

Is het mogelijk om als er nog geen waarde berekend is of de waarde onduidelijk is dat deze verborgen wordt?
dus dat de tekst ''#N/B'' of ''#Waarde!'' verborgen wordt?

#NB of #WAARDE! kan alleen verschijnen als ofwel in kolom K het aantal melders nog niet is ingevuld, of als je in een rij nog geen x hebt getypt. Afhankelijk van waaraan je de voorkeur geeft, kun je gebruiken:

- als het aantal melders nog niet is ingevuld:
=ALS(K3="";"";K3*0,01*(INDIRECT("AB"&KIEZEN(VERGELIJKEN("x";$L3:$W3;0);5;4;5;5;5;4;3;5;3;4;4))+INDIRECT("AC"&KIEZ EN(VERGELIJKEN("x";$L3:$W3;0);5;4;5;5;5;4;3;5;3;4;4))))

- als in een rij nog geen enkele x is ingevuld:
=ALS(AANTAL($L3:$W3)=0;"";K3*0,01*(INDIRECT("AB"&KIEZEN(VERGELIJKEN("x";$L3:$W3;0);5;4;5;5;5;4;3;5;3;4;4))+INDIRECT("AC"&KIEZ EN(VERGELIJKEN("x";$L3:$W3;0);5;4;5;5;5;4;3;5;3;4;4))))

 

[sanderc]

Jammer dat je dat niet eerder liet weten, want bovenstaande uitleg kostte mij anderhalf uur.
(en de oplossing eergisteren een aantal uren).

#NB of #WAARDE! kan alleen verschijnen als ofwel in kolom K het aantal melders nog niet is ingevuld, of als je in een rij nog geen x hebt getypt. Afhankelijk van waaraan je de voorkeur geeft, kun je gebruiken:

- als het aantal melders nog niet is ingevuld:
=ALS(K3="";"";K3*0,01*(INDIRECT("AB"&KIEZEN(VERGELIJKEN("x";$L3:$W3;0);5;4;5;5;5;4;3;5;3;4;4))+INDIRECT("AC"&KIEZ EN(VERGELIJKEN("x";$L3:$W3;0);5;4;5;5;5;4;3;5;3;4;4))))

- als in een rij nog geen enkele x is ingevuld:
=ALS(AANTAL($L3:$W3)=0;"";K3*0,01*(INDIRECT("AB"&KIEZEN(VERGELIJKEN("x";$L3:$W3;0);5;4;5;5;5;4;3;5;3;4;4))+INDIRECT("AC"&KIEZ EN(VERGELIJKEN("x";$L3:$W3;0);5;4;5;5;5;4;3;5;3;4;4))))

Sorry ik dacht dat dat bericht ook automatisch bij je zou verschijnen. ik wil je bij deze dan ook hartelijk bedanken voor je tijd en alle moeite je hebt voor mij weer een hele digitalisatie stap gezet. nogmaal hartelijk bedankt.