[Solved] Help with some complex formula
- Thread starter NikosBEG
- Start date
Hello NikosBEG,I would like if anyone can help me with this issueI was thinking about myself as an expert in Excel, but it seems I'm just a newbie
Welcome to ExcelDemy Forum! First of all, I would like to express my gratitude for providing a thorough explanation of the issue in the Excel file.
Regarding the calculation, you can utilize the formula below:
=IF(AND(Sheet1!D2="Male"),(Sheet1!I5-VLOOKUP(Sheet1!C2,Sheet2!A1:K85,5,FALSE))/VLOOKUP(Sheet1!C2,Sheet2!A1:K85,6,FALSE),0)
This formula displays the expected output as per the provided results. I have also attached the Excel file for better understanding. I hope this helps you. Good luck!
Regards,
Yousuf Shovon
Hi Yousuf,
thanks for your reply. This formula actually working for males only but not for females too.
C2 is age which will change from patient to patient and D2 is sex (male or female) and I5 is value which changing, but I need to enter to formula options of IF for C2 age and D2 sex.
It means that VLOOKUP should find result depends on both-sex and age cell. Actually lines of ages are in the same line, but sexes are in different columns.
ABSI-mean for males are located within column E on Sheet2, while ABSI-sd are within column F. For females ABSI-mean are located within column J and ABSI-sd within column K.
I'm sorry if I'm bothering you a lot
thanks for your reply. This formula actually working for males only but not for females too.
C2 is age which will change from patient to patient and D2 is sex (male or female) and I5 is value which changing, but I need to enter to formula options of IF for C2 age and D2 sex.
It means that VLOOKUP should find result depends on both-sex and age cell. Actually lines of ages are in the same line, but sexes are in different columns.
ABSI-mean for males are located within column E on Sheet2, while ABSI-sd are within column F. For females ABSI-mean are located within column J and ABSI-sd within column K.
I'm sorry if I'm bothering you a lot
Last edited:
Dear NikosBEG,Hi Yousuf,
thanks for your reply. This formula actually working for males only but not for females too.
C2 is age which will change from patient to patient and D2 is sex (male or female) and I5 is value which changing, but I need to enter to formula options of IF for C2 age and D2 sex.
It means that VLOOKUP should find result depends on both-sex and age cell. Actually lines of ages are in the same line, but sexes are in different columns.
ABSI-mean for males are located within column E on Sheet2, while ABSI-sd are within column F. For females ABSI-mean are located within column J and ABSI-sd within column K.
I'm sorry if I'm bothering you a lot
I sincerely apolozise for the misunderstanding. It seemed you wished to calculate ABSI z-score only for male cases. Thank you for reaching to us again. Your concerns are always appreciated in this community.
I am having trouble to understand the ABSI value in I5 though. Do we change this manually or through formula? Assuming ABSI static in this case, get the ABSI z-score value for both Male and Female cases with this formula:
=IF(AND(Sheet1!D2="Male"),(Sheet1!I5-VLOOKUP(Sheet1!C2,Sheet2!A2:K85,5,FALSE))/VLOOKUP(Sheet1!C2,Sheet2!A2:K85,6,FALSE),IF(AND(Sheet1!D2="Female"),(Sheet1!I5-VLOOKUP(Sheet1!C2,Sheet2!A2:K85,10,FALSE))/VLOOKUP(Sheet1!C2,Sheet2!A2:K85,11,FALSE),0))
This formula feedbacks ABSI z-score for both male and female cases based on their ages.
Attached is the Excel file for a thorough understanding. Don't hesitate to let us know if you are still facing any issue regarding this.
Regards,
Yousuf Shovon