j2curtis64
New Member
- Joined
- Oct 28, 2008
- Messages
- 33
Hi,
I am trying to do the following in excel but i am having a hard time. The formula is going to be too long.
I have two columns where i input data. One column (B)is GOV or KTR and the other column (C) you could have L, O, or T. The next column i concatenate columns B+C. The value will determine the cell referenced in the other sheet (rate_table). After the cell that is referenced from the rate_table that amount is multipled by the columns referenced in the SUM function.
Any quicker or efficient way to do this?
=IF(($D$22="GOVL"),Rate_Table!F6,IF(($D$22="GOVO"),Rate_Table!F10,IF(($D$22="GOVT"),Rate_Table!F10,IF(($D$22="KTRL"),Rate_Table!F10,IF(($D$22="KTRO"),Rate_Table!F10)))))*SUM(J22:M22)+
IF(($D$22="GOVL"),Rate_Table!F6,IF(($D$22="GOVO"),Rate_Table!F10,IF(($D$22="GOVT"),Rate_Table!F10,IF(($D$22="KTRL"),Rate_Table!F10,IF(($D$22="KTRO"),Rate_Table!F10)))))*SUM(N22:Q22)+
IF(($D$22="GOVL"),Rate_Table!F6,IF(($D$22="GOVO"),Rate_Table!F10,IF(($D$22="GOVT"),Rate_Table!F10,IF(($D$22="KTRL"),Rate_Table!F10,IF(($D$22="KTRO"),Rate_Table!F10)))))*SUM(R22:U22)
+IF(($D$22="GOVL"),Rate_Table!F6,IF(($D$22="GOVO"),Rate_Table!F10,IF(($D$22="GOVT"),Rate_Table!F10,IF(($D$22="KTRL"),Rate_Table!F10,IF(($D$22="KTRO"),Rate_Table!F10)))))*SUM(V22:Y22)
Thanks for your assistance
I am trying to do the following in excel but i am having a hard time. The formula is going to be too long.
I have two columns where i input data. One column (B)is GOV or KTR and the other column (C) you could have L, O, or T. The next column i concatenate columns B+C. The value will determine the cell referenced in the other sheet (rate_table). After the cell that is referenced from the rate_table that amount is multipled by the columns referenced in the SUM function.
Any quicker or efficient way to do this?
=IF(($D$22="GOVL"),Rate_Table!F6,IF(($D$22="GOVO"),Rate_Table!F10,IF(($D$22="GOVT"),Rate_Table!F10,IF(($D$22="KTRL"),Rate_Table!F10,IF(($D$22="KTRO"),Rate_Table!F10)))))*SUM(J22:M22)+
IF(($D$22="GOVL"),Rate_Table!F6,IF(($D$22="GOVO"),Rate_Table!F10,IF(($D$22="GOVT"),Rate_Table!F10,IF(($D$22="KTRL"),Rate_Table!F10,IF(($D$22="KTRO"),Rate_Table!F10)))))*SUM(N22:Q22)+
IF(($D$22="GOVL"),Rate_Table!F6,IF(($D$22="GOVO"),Rate_Table!F10,IF(($D$22="GOVT"),Rate_Table!F10,IF(($D$22="KTRL"),Rate_Table!F10,IF(($D$22="KTRO"),Rate_Table!F10)))))*SUM(R22:U22)
+IF(($D$22="GOVL"),Rate_Table!F6,IF(($D$22="GOVO"),Rate_Table!F10,IF(($D$22="GOVT"),Rate_Table!F10,IF(($D$22="KTRL"),Rate_Table!F10,IF(($D$22="KTRO"),Rate_Table!F10)))))*SUM(V22:Y22)
Thanks for your assistance