• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

FORMULA HELP

B

BlackLotus

New Member
I think I need some combination of an if and lookup statement, but I can't find the right combination of functions to achieve the result I want. I copy and pasted the file below. I couldn't get the file upload to work. I would like for the Kiwi column to do the following:

If the variant columns contain the same value then the formula in the kiwi column should be SQRT(((0.7*PEACH)/(PI()/4))+PEAR^2) but the PEACH term should use the maximum value out of the options with the same variant.

Example: For the row 2 where the variant equals 1 the kiwi cell should use the formula SQRT(((0.7*1.842)/PI()/4))+2.170. For row 3 the variant cell would use the formula SQRT(((0.7*1.842)/PI()/4))+2.000, and so on.

I think I confused myself typing this, LOL, so if anything is unclear please let me know. TIA

APPLE ORANGE VARIANT BANANA PEAR PLUM PEACH KIWI
2.500 0.115 1 2.270 2.170 2.220 0.862
2.500 0.200 1 2.100 2.000 2.050 1.445
2.500 0.262 1 1.976 1.876 1.926 1.842
2.500 0.325 2 1.850 1.750 1.800 2.221
2.500 0.387 2 1.726 1.626 1.676 2.569
2.500 0.550 3 1.400 1.300 1.350 3.369

​
 
Hi,

Well really it is confusing, but try below array formula in H2 and copy down:

=SQRT(((0.7*MAX(IF(C2=$C$2:$C$7,$G$2:$G$7)))/(PI()/4))+E2^2)

Enter with Ctrl+Shift+Enter.

Other wise write back. And you can upload an excel file here of size upto 1 MB max.

Regards,
 
Somendra Misra said:
Hi,

Well really it is confusing, but try below array formula in H2 and copy down:

=SQRT(((0.7*MAX(IF(C2=$C$2:$C$7,$G$2:$G$7)))/(PI()/4))+E2^2)

Enter with Ctrl+Shift+Enter.

Other wise write back. And you can upload an excel file here of size upto 1 MB max.

Regards,
Click to expand...


This formula wasn't exactly what I was looking for, but it was close enough for me to tweak. I'm still working on playing with different variables. This was a miniscule part of a massive spreadsheet I am working on at my job hence the silly names. Once I perfect it I will post the formula of the exact solution that worked. Thanks!
 
You must log in or register to reply here.
Back
Top