ExCel Nested IF Statement Generator

[Metcalfe]

Hello, I am struggling with a rather large if statement that checks several criteria, is there a tool to help me with this as it is very tedious?

Thanks

[Metcalfe]

This is the information I am trying to input;

1. IF Y7<>”N/A”
2. IF U7=4:
TRUE: PERCENTRANK.INC(IF('AIS Statistics'!$R$5:$R$4732<>0,'AIS Statistics'!$R$5:$R$4732),Y7,5))
3. IF U7=5:
TRUE: PERCENTRANK.INC(IF('AIS Statistics'!$W$5:$W$7973<>0,'AIS Statistics'!$W$5:$W$7973),Y7,5))
4. IF U7=6:
TRUE: PERCENTRANK.INC(IF('AIS Statistics'!$AB$5:$AB$933<>0,'AIS Statistics'!$AB$5:$AB$933),Y7,5))
5. IF U7=7:
TRUE: PERCENTRANK.INC(IF('AIS Statistics'!$AG$5:$AG$2327<>0,'AIS Statistics'!$AG$5:$AG$2327),Y7,5))
6. IF U7=8:
TRUE: PERCENTRANK.INC(IF('AIS Statistics'!$AL$5:$AL$98<>0,'AIS Statistics'!$AL$5:$AL$98),Y7,5))
7. IF U7=9:
TRUE: PERCENTRANK.INC(IF('AIS Statistics'!$AQ$5:$AQ$14495<>0,'AIS Statistics'!$AQ$5:$AQ$14495),Y7,5))
8. IF U7=10:
TRUE: PERCENTRANK.INC(IF('AIS Statistics'!$AV$5:$AV$9844<>0,'AIS Statistics'!$AV$5:$AV$9844),Y7,5))
9. IF FALSE: “N/A”

[Kaper]

See:

Please Login or Register  to view this content.

The other option could be use OFFSET to use appropriate column in Statistics But it would be easier to propose if you show (attach) sample file

[Metcalfe]

See:

Please Login or Register  to view this content.

The other option could be use OFFSET to use appropriate column in Statistics But it would be easier to propose if you show (attach) sample file

Kaper, many thanks for your assistance - again!

There are too many arguments in the function at present, please see Test Sample Sheet attached.

In summary if column B has a value I want to calculate its percentile rank in its designated shipping section. All data for each section is provide on the AIS statistics tab.

[José Augusto]

Try this formula

Please Login or Register  to view this content.

[samba_ravi]

Please Login or Register  to view this content.

Same as Mr. Kaper but little difference

[Kaper]

1) No attachment
2) I think José Augusto explored/implemented my second proposition :-)

[Metcalfe]

Sorry, the file was too large.

I have now attached a shortened version.

I am struggling to apply the already provided formulas into my sheet due to lack of expertise therefore I would be grateful if you could use the attached which I can modify with ease.

Thanks

[JeteMc]

It is hard, for me at least, to figure out which cell in the attached file is supposed to correspond to Y7 (I suppose A4 corresponds to U7) mentioned in the first post (as both of those cells are blank on the 'Interpretation Rev 1' sheet in the attached file)
It might help if you could manually mock up one or two of your expected results.
The more information that you can supply as to how the expected results are derived, the more helpful it will be to someone attempting to provide a solution.
Let us know if you have any questions.