Assistance with developing complex formula

[sanspm]

I'm hoping one of you wizards can help me out with something that is likely very easy if you know what you're doing.

This will be complex to explain, and I'll try to not complicate with potentially extraneous information.

Columns B-U are a weekly measurement called WHZ, where:
WHZ is < -3 is conditionally formatted red
WHZ is >= -3 and < -2 is conditionally formatted gold
WHZ is >= -2 is conditionally formatted green

Columns V-AO are the weekly weight measurements.

Columns AP-BI are what I'm seeking help for.

What I need help with is figuring out the formula to return the correct dosage of a food product based on the child's weekly WHZ and weight, based on the following:

- if WHZ is <-3 then the formula should use the dosage by weight formula (see below)
- if WHZ is >=-3 for one visit, the formula should also use the dosage by weight formula (see below)
- if WHZ is >=-3 for two consecutive weeks, then the formula should return a value of 14 (NB: it's important that it is consecutive weeks, if not then the child follows the dosage by weight formula; it's the consecutive weeks requirement that is causing me trouble)

Dosage by weight formula:
=IF(V2>=12,35,IF(V2>=10.5,32,IF(V2>=9.5,28,IF(V2>=8.5,25,IF(V2>=7.0,21,IF(V2>=5.0,18,IF(V2>=4.0,14,IF(V2>=3.0,11,""))))))))

In semi-plain English, the child should receive a dosage based on their weight until they have a WHZ >=-3 for two consecutive visits. Once they have WHZ >=-3 for two consecutive visits then they get 14 sachets of product regardless of their weight. Since growth is not linear, their WHZ might drop below -3, and then they have to go back to a weight based dosage until they reach WHZ >=-3 for two consecutive visits again.

In columns BJ-BX I’ve manually calculated what the values should be with the correct formula to help with cross-checking that it is working correctly.

Any assistance with creating this formula would be greatly appreciated!

[bebo021999]

Your words said WHZ>=-3, but your manual outcome said >=-2?

I assum your manual outcome are correct, try in AP2:

Please Login or Register  to view this content.

Correct -2 into -3 which is correct.

Drag down and accross

[sanspm]

Thank you very much! This formula is exactly what I was looking for.