Hi all,
I am working on transferring 3 expressions from Excel to QGIS. To do this I
need to change the syntax and the references/variables of my expressions.
Different Excel functions are used in all 3 expressions, and I can't find them
in QGIS. I've looked online for some guidance but didn't find much. Does QGIS
even have these functions in the field calculator?
Here are the functions I used in Excel:
AVERAGE
NORM.DIST
LN
IF
OR
TRUE
FALSE
IFERROR
VLOOKUP
I also used the dollar sign which works as an absolute cell reference in Excel
formulas. Does QGIS have a similar function?
I am very new to QGIS and not even sure if what I want to do is even possible.
My goal is to transfer these expressions in QGIS, along with the attribute
tables and the data they're linked to. In theory, once the transfer is
complete, the expressions would stay in QGIS and if or when the data is updated
or newer/more precise data is available, I could update the contents of the
expressions, but not the functions themselves.
Here are the equations as they are formatted in Excel:
1.
=IF(Options!$C$8=Options!$P$8,NORM.DIST(LN(AVERAGE($P7)),LN(Q7),R7,TRUE),NORM.DIST(LN($F7),LN($D7),$E7,TRUE)*NORM.DIST(LN($F7/2),LN(Q7),R7,TRUE)+
(NORM.DIST(LN($G7),LN($D7),$E7,TRUE)-NORM.DIST(LN($F7),LN($D7),$E7,TRUE))*NORM.DIST(LN(AVERAGE($F7:$G7)),LN(Q7),R7,TRUE)+
(NORM.DIST(LN($H7),LN($D7),$E7,TRUE)-NORM.DIST(LN($G7),LN($D7),$E7,TRUE))*NORM.DIST(LN(AVERAGE($G7:$H7)),LN(Q7),R7,TRUE)+
(NORM.DIST(LN($I7),LN($D7),$E7,TRUE)-NORM.DIST(LN($H7),LN($D7),$E7,TRUE))*NORM.DIST(LN(AVERAGE($H7:$I7)),LN(Q7),R7,TRUE)+
(NORM.DIST(LN($J7),LN($D7),$E7,TRUE)-NORM.DIST(LN($I7),LN($D7),$E7,TRUE))*NORM.DIST(LN(AVERAGE($I7:$J7)),LN(Q7),R7,TRUE)+
(NORM.DIST(LN($K7),LN($D7),$E7,TRUE)-NORM.DIST(LN($J7),LN($D7),$E7,TRUE))*NORM.DIST(LN(AVERAGE($J7:$K7)),LN(Q7),R7,TRUE)+
(NORM.DIST(LN($L7),LN($D7),$E7,TRUE)-NORM.DIST(LN($K7),LN($D7),$E7,TRUE))*NORM.DIST(LN(AVERAGE($K7:$L7)),LN(Q7),R7,TRUE)+
(NORM.DIST(LN($M7),LN($D7),$E7,TRUE)-NORM.DIST(LN($L7),LN($D7),$E7,TRUE))*NORM.DIST(LN(AVERAGE($L7:$M7)),LN(Q7),R7,TRUE)+
(NORM.DIST(LN($N7),LN($D7),$E7,TRUE)-NORM.DIST(LN($M7),LN($D7),$E7,TRUE))*NORM.DIST(LN(AVERAGE($M7:$N7)),LN(Q7),R7,TRUE)+
(NORM.DIST(LN($O7),LN($D7),$E7,TRUE)-NORM.DIST(LN($N7),LN($D7),$E7,TRUE))*NORM.DIST(LN(AVERAGE($N7:$O7)),LN(Q7),R7,TRUE)+
(1-NORM.DIST(LN($O7),LN($D7),$E7,TRUE))*NORM.DIST(LN(AVERAGE($O7)),LN(Q7),R7,TRUE))
1.
=IFERROR('Options'!Y3,0)*(0.02*(AC7-AD7)+0.1*(AD7-AE7)+0.4*(AE7-AF7)+AF7)
2.
=IF(OR($NE6="OTF1",$NE6="OTF2",$NE6="SPRUNG
SHELTER"),0,($PC6-$PQ6)*(VLOOKUP($NE6,'HAZUS - Collapse% +
Casualties'!$B$14:$F$49,RW$4,FALSE)/100)+($PQ6-$QE6)*(VLOOKUP($NE6,'HAZUS -
Collapse% +
Casualties'!$B$55:$F$90,RW$4,FALSE)/100)+($QE6-$QS6)*(VLOOKUP($NE6,'HAZUS -
Collapse% +
Casualties'!$B$96:$F$131,RW$4,FALSE)/100)+($QS6-$QU6)*(VLOOKUP($NE6,'HAZUS -
Collapse% +
Casualties'!$B$181:$F$216,RW$4,FALSE)/100)+($QU6)*(VLOOKUP($NE6,'HAZUS -
Collapse% + Casualties'!$B$181:$F$216,RW$4,FALSE)/100))
I appreciate any and all help!
Thanks,
Simon
_______________________________________________
QGIS-User mailing list
[email protected]
List info: https://lists.osgeo.org/mailman/listinfo/qgis-user
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
