Oct 01 2021 10:28 PM
Oct 01 2021 10:47 PM
@Rossman1 Perhaps you can use this:
IF(A1<10, A1, "")
That will return the value of A1 if the condition is met. Otherwise, it will return nothing.
Oct 01 2021 11:05 PM
Oct 01 2021 11:58 PM
@Rossman1 when you write "Let's say A1 is the sum of 5 numbers.......", I wonder if it really is so. Why not upload a file with the exact same structure as what you are dealing with. Then, it will be a lot easier to come up with a working solution, that probably requires some VBA programming. Having said that, VBA isn't really my favourite way to spend time. Someone else can step in to help you.
Oct 04 2021 11:02 AM
Here is a copy of the file.
Oct 04 2021 10:34 PM
@Rossman1 Sorry! Can't help you with triggering a recalculation if a range adds up to more than a certain amount. As said, that would probably require VBA.
But you might find one formula simplification helpful. Since your Excel version seems to support dynamic arrays, change this monster of a formula:
=SUM(IF(E4=$B$4,$A$4,IF(E4=$B$5,$A$5,IF(E4=$B$6,$A$6,IF(E4=$B$7,$A$7,IF(E4=$B$8,$A$8,IF(E4=$B$9,$A$9,IF(E4=$B$10,$A$10,IF(E4=$B$11,$A$11,IF(E4=$B$12,$A$12,IF(E4=$B$13,$A$13)))))))))),IF(E5=$B$4,$A$4,IF(E5=$B$5,$A$5,IF(E5=$B$6,$A$6,IF(E5=$B$7,$A$7,IF(E5=$B$8,$A$8,IF(E5=$B$9,$A$9,IF(E5=$B$10,$A$10,IF(E5=$B$11,$A$11,IF(E5=$B$12,$A$12,IF(E5=$B$13,$A$13)))))))))),IF(E6=$B$4,$A$4,IF(E6=$B$5,$A$5,IF(E6=$B$6,$A$6,IF(E6=$B$7,$A$7,IF(E6=$B$8,$A$8,IF(E6=$B$9,$A$9,IF(E6=$B$10,$A$10,IF(E6=$B$11,$A$11,IF(E6=$B$12,$A$12,IF(E6=$B$13,$A$13)))))))))),IF(E7=$B$4,$A$4,IF(E7=$B$5,$A$5,IF(E7=$B$6,$A$6,IF(E7=$B$7,$A$7,IF(E7=$B$8,$A$8,IF(E7=$B$9,$A$9,IF(E7=$B$10,$A$10,IF(E7=$B$11,$A$11,IF(E7=$B$12,$A$12,IF(E7=$B$13,$A$13)))))))))),IF(E8=$B$4,$A$4,IF(E8=$B$5,$A$5,IF(E8=$B$6,$A$6,IF(E8=$B$7,$A$7,IF(E8=$B$8,$A$8,IF(E8=$B$9,$A$9,IF(E8=$B$10,$A$10,IF(E8=$B$11,$A$11,IF(E8=$B$12,$A$12,IF(E8=$B$13,$A$13)))))))))))
to this:
=SUM(XLOOKUP(E4#,options,costs))
where "options" and "costs" are named ranges to the obvious lookup and return ranges.