[Qgis-user] Excel functions in QGIS

[SIMON.COTE2 at ecn.forces.gc.ca]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20230711/9a2a87d4/attachment.htm>