help to make formula

J

Jamesneedshelp

New Member
Joined
Jul 2, 2012
Messages
11
Hi,<o:p></o:p>
I am hoping someone can help me with a macro or formula that will help me achieve the following. Basically the 3rd column (where it says example) shows what i am trying to calculate. The areas in blue and green will actually be in other excel sheets but if you make the formula as if it is all in one sheet i should be able to change it so it references external sheets. Im guessing an IF formula may work but im really not sure. It seems like it should be pretty simple for someone to work out. The answer would go where it says conversion.
Thanks,

Job NoConversionexampleJob NoQtyJob NoCosts
1S(1S x 1C) /
(1S +1F +1L)		1S500.001C2,000.00
2S(2S x 2C) /
(2S +2 F + 2L)		2S500.002C2,000.00
3S 3S500.003C2,000.00
4S 4S500.004C2,000.00
1F 1F500.00
2F 2F500.00
3F 3F700.00
4F 4F700.00
1L 1L700.00
2L 2L700.00
3L 3L700.00
4L 4L700.00

<COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 2784" width=87><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 4640" width=145><COL style="WIDTH: 54pt" width=72><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 2912" width=91><COL style="WIDTH: 54pt" width=72><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3136" width=98><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 2880" width=90><COL style="WIDTH: 54pt" span=3 width=72><TBODY>
</TBODY>
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Code: 
       --A--- ----B----- ---C--- D E --F--- -G- H --I--- ---J----
   1   Job No Conversion example     Job No Qty   Job No  Costs  
   2   1S                 588.24     1S     500   1C     2,000.00
   3   2S                 588.24     2S     500   2C     2,000.00
   4   3S                 526.32     3S     500   3C     2,000.00
   5   4S                 526.32     4S     500   4C     2,000.00
   6                                                             
   7   1F                            1F     500                  
   8   2F                            2F     500                  
   9   3F                            3F     700                  
  10   4F                            4F     700                  
  11                                                             
  12   1L                            1L     700                  
  13   2L                            2L     700                  
  14   3L                            3L     700                  
  15   4L                            4L     700
The formula in C2 and copied down is

=G2*J2 / (G2+G7+G12)
 
Upvote 0
Thanks for trying but this wont work. It needs to search down column H (Job No for costs) and only use figures corresponding to specific Job No. So i think and IF function is needed.:)
shg said:
Code: 
       --A--- ----B----- ---C--- D E --F--- -G- H --I--- ---J----
   1   Job No Conversion example     Job No Qty   Job No  Costs  
   2   1S                 588.24     1S     500   1C     2,000.00
   3   2S                 588.24     2S     500   2C     2,000.00
   4   3S                 526.32     3S     500   3C     2,000.00
   5   4S                 526.32     4S     500   4C     2,000.00
   6                                                             
   7   1F                            1F     500                  
   8   2F                            2F     500                  
   9   3F                            3F     700                  
  10   4F                            4F     700                  
  11                                                             
  12   1L                            1L     700                  
  13   2L                            2L     700                  
  14   3L                            3L     700                  
  15   4L                            4L     700
The formula in C2 and copied down is

=G2*J2 / (G2+G7+G12)
Click to expand...
 
Upvote 0
Code: 
       --A--- -----B----- C D E --F--- -G- H --I--- --J--
   1   Job No Conversion        Job No Qty   Job No Costs
   2   1S     588.2352941       1S     500   1C      2000
   3   2S                       2S     500   2C      2000
   4   3S                       3S     500   3C      2000
   5   4S                       4S     500   4C      2000
   6                                                     
   7   1F                       1F     500               
   8   2F                       2F     500               
   9   3F                       3F     700               
  10   4F                       4F     700               
  11                                                     
  12   1L                       1L     700               
  13   2L                       2L     700               
  14   3L                       3L     700               
  15   4L                       4L     700


B2: =VLOOKUP(A2,F:G,2,FALSE) * VLOOKUP(LEFT(A2) & "C",I:J, 2, FALSE) / (VLOOKUP(A2,F:G,2,FALSE) + VLOOKUP(LEFT(A2) & "F",F:G, 2, FALSE) + VLOOKUP(LEFT(A2) & "L",F:G, 2, FALSE))
 
Upvote 0
shg said:
Code: 
       --A--- -----B----- C D E --F--- -G- H --I--- --J--
   1   Job No Conversion        Job No Qty   Job No Costs
   2   1S     588.2352941       1S     500   1C      2000
   3   2S                       2S     500   2C      2000
   4   3S                       3S     500   3C      2000
   5   4S                       4S     500   4C      2000
   6                                                     
   7   1F                       1F     500               
   8   2F                       2F     500               
   9   3F                       3F     700               
  10   4F                       4F     700               
  11                                                     
  12   1L                       1L     700               
  13   2L                       2L     700               
  14   3L                       3L     700               
  15   4L                       4L     700


B2: =VLOOKUP(A2,F:G,2,FALSE) * VLOOKUP(LEFT(A2) & "C",I:J, 2, FALSE) / (VLOOKUP(A2,F:G,2,FALSE) + VLOOKUP(LEFT(A2) & "F",F:G, 2, FALSE) + VLOOKUP(LEFT(A2) & "L",F:G, 2, FALSE))
Click to expand...

Thanks, ill try it out
 
Upvote 0
It worked down to row 8 but then it starts to give wrong answers, so it looks like its on the right track but do you know what might be causing this. On the top sheet is the correct answers and on the bottom is the answers your formula gives. Thanks, it looks close.

Job No.ConversionType
1101S588.2352941State
1105S588.2352941State
1106S526.3157895State
3201S526.3157895State
1101F588.2352941Federal
1105F588.2352941Federal
1106F736.8421053Federal
3201F736.8421053Federal
1101L823.5294118Local
1105L823.5294118Local
1106L736.8421053Local
3201L736.8421053Local

<COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 2784" width=87><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4000" width=125><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 2880" width=90><TBODY>
</TBODY>
Job NoConversionexample
1S588.2352941(1S x 1C) /
(1S +1F +1L)
2S588.2352941(2S x 2C) /
(2S +2 F + 2L)
3S526.3157895
4S526.3157895
#N/A
1F588.2352941
2F588.2352941
3F666.6666667
4F666.6666667
#N/A
1L736.8421053
2L736.8421053
3L666.6666667
4L666.6666667

<COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 2784" width=87><COL style="WIDTH: 109pt; mso-width-source: userset; mso-width-alt: 4640" width=145><COL style="WIDTH: 54pt" width=72><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 2912" width=91><TBODY>
</TBODY>
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Group of cells background colouring using VBA
Replies
5
Views
396
GWteB
GWteB
G
  • Question
Conditional formating where there are 2 values in a cell
Replies
3
Views
932
Godders199
G
S
  • Question
Locating duplicate rows in Excel
Replies
1
Views
506
JoeMo
J
S
  • Question
Conditional Formating Required for changing Dates based on 2 different cell dates
Replies
1
Views
700
Toadstool
Toadstool
B
  • Question
Matching Number To Number In text
2 3
Replies
23
Views
2K
Twollaston
T

Forum statistics

Threads
1,215,482
Messages
6,125,058
Members
449,206
Latest member
Healthydogs

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top