| Home |
| Search |
| Today's Posts |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Totaling cells consisting of values determined through concatenate
Excellent. Worked flawlessly. Thank you!
On Apr 9, 12:24 pm, Joel wrote: Make a special function like the one below. It returns a number, not text. format the cell for currency. call functtion as follows =GetTotal(J7,L7) Function GetTotal(Parm1, Parm2) Select Case Parm1 Case 4.1 GetTotal = Parm2 * 0.6 * 140 Case 4.2 GetTotal = Parm2 * 0.4 * 140 Case 4.22 GetTotal = Parm2 * 0.4 * 145 Case 5.1 GetTotal = Parm2 * 0.6 * 135 Case 5.2 GetTotal = Parm2 * 0.4 * 135 Case 5.22 GetTotal = Parm2 * 0.4 * 135 Case 8 GetTotal = Parm2 * 170 Case 9 GetTotal = Parm2 * 120 Case 10 GetTotal = 130 Case Else GetTotal = 0 End Select End Function " wrote: I have a worksheet with 40 rows (this number of rows will not be static). Each row, in column "M" needs to cycle through about 10 IF statements. To accomplish this, I used the CONCATENATE function. The format of those cells needs to be as currency, so the CONCATENATE function is wrapped in a TEXT format. Here is the formula below: =TEXT(CONCATENATE(IF(J7=4.1,(L7*0.6)*140,""),IF(J7 =4.2, (L7*0.4)*140,""),IF(J7=4.22,(L7*0.4)*145,""),IF(J7 =5.1, (L7*0.6)*135,""),IF(J7=5.22,(L7*0.4)*135,""),IF(J7 =5.2, (L7*0.4)*135,""),IF(J7=8,L7*170,""),IF(J7=9,L7*120 ,""),IF(J7=10,130,0)),"$#*,##0.00") I now want to sum the values in those rows (all the values that need to be summed are in column "M"). but do not seem to be able to because of the Concatenate function. Can someone give me guidance regarding how to get the cell values to sum together? Thanks.- Hide quoted text - - Show quoted text - |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Saving a pre-determined range of cells as text? | Excel Discussion (Misc queries) | |||
| IF= Cells not totaling | Excel Worksheet Functions | |||
| Copy cells in a determined order | Excel Discussion (Misc queries) | |||
| Random Values totaling Cell value | Excel Discussion (Misc queries) | |||
| tabbing to pre-determined cells | Excel Discussion (Misc queries) | |||
Threaded Mode
