Help with complex formula, with XLOOKUP

[sanspm]

I'm hoping that somebody smarter than me can help me out with a complicated formula I'm having trouble designing. Attached is the file.

Cell R16 should calculate the total kg of a medicine called "F100" based on the number of days in treatment (user entered data in R13), the average daily weight gain (user entered data in R14), and weight of the patient at admission (user entered data in R4). The weight-based dosage chart of F100 is in columns B-G.


The formula should look something like this:
On day 1 of treatment, the patient should get the amount listed in the dosage chart (Column G based on their weight that day) +
On day 2 of treatment, the patient should get the amount listed in the dosage chart (Column G based on their weight that day, which is weight at admission plus the weight gained since admission) +
.....
On day N of treatment (cell R13), the patient should get the amount listed in the dosage chart (Column G based on their weight that day, which is weight at admission plus the weight gained since admission)
= The total amount provided to the patient based on their weight at admission and average daily weight gain

Many thanks for any suggestions!

[WideBoyDixon]

The daily weight gain is given as 10 so after 1 day the weight will be 15.1kg and after 45 days it will be 455.1kg - that's quite a lot! Perhaps daily weight gain is measure in something else?

WBD

[sanspm]

The daily weight gain is given as 10 so after 1 day the weight will be 15.1kg and after 45 days it will be 455.1kg - that's quite a lot! Perhaps daily weight gain is measure in something else?

WBD

Ha! Good catch! That weight gain is usually reported as grams per kg of body weight per day. When I tried to develop the formula myself I made sure to divide that by 1000.

[WideBoyDixon]

Give this a try then:

=SUMPRODUCT(XLOOKUP(R4+R14*(ROW($A$1:INDEX($A:$A,R13))-1)/1000,B6:B26,G6:G26,,-1))

WBD

[sanspm]

Thanks WBD!

This almost works, but it looks like I made a mistake in the way I explained how the weight is calculated. The incremental daily weight gain is on "yesterday's" weight and not on the starting weight.

I plugged in your formula into R16 and I did a manual calculation to cross-check the results to see if it works as expected. Ultimately the formula should return a value of 2.64 rather than 2.11. Is that a relatively easy modification or does it overly complicate things?

Reattaching the worksheet with your formula and my semi-manual calculation.

[FlameRetired]

Administrative Note:

Welcome to the forum.

sanspm We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[sanspm]

I was instructed to change the title of my thread. I don't really know the right terms to use, but hopefully this is enough to re-open my request for help.

[WideBoyDixon]

It seems like you made a change to the Daily weight gain - you initially said this was measured in grams but your new calculation has this as a percentage weight gain! In which case I think this is correct according to your manual calculation:

=SUMPRODUCT(MAXIFS($G$6:$G$96,$B$6:$B$96,"<="&R4*(1+$R$14/100)^(ROW($A$1:INDEX($A:$A,R13+1))-1)))

WBD

[sanspm]

Thanks for your ongoing help WBD and sorry for any confusion caused by my miscommunication / formula errors! I hope this is the last time I ask for your help...

It *is* actually grams and not percentage, based on the previous day's weight, and is x grams gain per kg of body weight per day.

In this particular example it's a gain of 5 grams per kg of body weight per day. So, a child starting treatment at 6kg:
would presumably gain 30.0 grams by the second day of treatment to be 6.03 kg
and then would gain another 30.1 grams by the third day of treatment to be 6.06 kg
and then would gain another 30.3 grams by the fourth day of treatment to be 6.09 kg
.....

Thanks for your patience!

[WideBoyDixon]

Ah. OK. Then just change the 100 to 1000 above:

=SUMPRODUCT(MAXIFS($G$6:$G$96,$B$6:$B$96,"<="&R4*(1+$R$14/1000)^(ROW($A$1:INDEX($A:$A,R13+1))-1)))

WBD

[WideBoyDixon]

So then I started poking around at the calculation in column G which, after simplification, is [Column B]*37/50 so I came up with another couple of formulas. The first one performs the same calculation as above without using the lookup table:

=SUMPRODUCT(FLOOR.MATH(R4*(1+$R$14/1000)^(ROW($A$1:INDEX($A:$A,R13+1))-1),0.2)*37/1050)

The second one is based on the calculation against the exact weight (without using the 0.2 increments) and disposes of the FLOOR.MATH part:

=SUMPRODUCT(R4*(1+$R$14/1000)^(ROW($A$1:INDEX($A:$A,R13+1))-1)*37/1050)

Just my natural mathematical curiosity coming to the fore ...

WBD

[sanspm]

Many, many thanks WBD! I never would have figured this out without your help.

The version that I think makes the most sense is the one that uses the lookup table because the "dosage" table that is actually used might be slightly different from what I have in the sample data. I'll plug this one into the workbook.

Thanks again!