Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to get a Boolean formula to work month-to-month
I'm using Excel 2003, and having the following problem:
(though I may have put this in in the wrong question group; if so, I am sorry) I'm trying to make a formula (or multiple ones, if necessary) that, by plugging in two numbers (a total revenue value & the first day of the month), it will create a set of percentages already assigned. So far, I've got it set like this: =IF(Sheet1!G6=1,SUM(Sheet1!D7*0.02),IF(Sheet1!G7=1 ,SUM(Sheet1!D7*0.044),IF(Sheet1!G8=1,SUM(Sheet1!D7 *0.054),IF(Sheet1!G9=1,SUM(Sheet1!D7*0.059),IF(She et1!G10=1,SUM(Sheet1!D7*0.049),IF(Sheet1!G11=1,SUM (Sheet1!D7*0.027),IF(Sheet1!G12=1,SUM(Sheet1!D7*0. 024)))))))) Where SHEET1!G6 = Sunday, G7 = Monday, G8 = Tuesday, G9 = Wednesday, G10 = Thursday, G11 = Friday, G12 = Saturday, and D7 = revenue value. However, whenever I plug in this into where I'd like the numbers to come out, it puts the same value into every cell it's entered into. What I'm hoping for is that if I plug in the revenue value into D7, and 1 into the Sunday value (G6) it will put the Sunday percentage (0.02) into the 1st, 8th, 15th, 22nd, and 29th day cells, the Monday percentage (0.044) into the 2nd, 9th, 16th, 23rd, and 30th day cells, and so on with all seven days. And if I put a 1 in the Monday value (G7), it will re-figure those percentages so that the Monday percentage would fall on the 1st, 8th, 15th, 22nd, and 29th, etc. Can someone please help me with this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to get a Boolean formula to work month-to-month
Which cells contain your formula? Is it not possible to put the day of the
week into a single cell and do a VLOOKUP to get the percentage for a given day? "mstieler" wrote: I'm using Excel 2003, and having the following problem: (though I may have put this in in the wrong question group; if so, I am sorry) I'm trying to make a formula (or multiple ones, if necessary) that, by plugging in two numbers (a total revenue value & the first day of the month), it will create a set of percentages already assigned. So far, I've got it set like this: =IF(Sheet1!G6=1,SUM(Sheet1!D7*0.02),IF(Sheet1!G7=1 ,SUM(Sheet1!D7*0.044),IF(Sheet1!G8=1,SUM(Sheet1!D7 *0.054),IF(Sheet1!G9=1,SUM(Sheet1!D7*0.059),IF(She et1!G10=1,SUM(Sheet1!D7*0.049),IF(Sheet1!G11=1,SUM (Sheet1!D7*0.027),IF(Sheet1!G12=1,SUM(Sheet1!D7*0. 024)))))))) Where SHEET1!G6 = Sunday, G7 = Monday, G8 = Tuesday, G9 = Wednesday, G10 = Thursday, G11 = Friday, G12 = Saturday, and D7 = revenue value. However, whenever I plug in this into where I'd like the numbers to come out, it puts the same value into every cell it's entered into. What I'm hoping for is that if I plug in the revenue value into D7, and 1 into the Sunday value (G6) it will put the Sunday percentage (0.02) into the 1st, 8th, 15th, 22nd, and 29th day cells, the Monday percentage (0.044) into the 2nd, 9th, 16th, 23rd, and 30th day cells, and so on with all seven days. And if I put a 1 in the Monday value (G7), it will re-figure those percentages so that the Monday percentage would fall on the 1st, 8th, 15th, 22nd, and 29th, etc. Can someone please help me with this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to get a Boolean formula to work month-to-month
Cells G4 through G34 (just in the G column) on multiple pages. I've got 3
formulas so far; two formulas are for two pages each, the other is just for one page. I'm a little confused on VLOOKUP. How do you mean? "Toppers" wrote: Which cells contain your formula? Is it not possible to put the day of the week into a single cell and do a VLOOKUP to get the percentage for a given day? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to get a Boolean formula to work month-to-month
For VLOOKUP:
A table in Columns A & B of (say) Sheet3 Sunday 0.020 Monday 0.044 Tuesday 0.053 ...... With day in G6 as an example (Monday) =VLOOKUP(G6,Sheet3!A1:B7,2,0) will return % for Monday This may remove the need for your IF statement. I am still not sure why the days are in separate cells nor how the formula replicates the 7 day cycle. "mstieler" wrote: Cells G4 through G34 (just in the G column) on multiple pages. I've got 3 formulas so far; two formulas are for two pages each, the other is just for one page. I'm a little confused on VLOOKUP. How do you mean? "Toppers" wrote: Which cells contain your formula? Is it not possible to put the day of the week into a single cell and do a VLOOKUP to get the percentage for a given day? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to get a Boolean formula to work month-to-month
Well, thanks for the help, Toppers, but I came up with another idea, and it
seems to work. I've got seven cells, one for each day: Sunday = G6 Monday = G7 Tuesday = G8 Wednesday = G9 Thursday = G10 Friday = G11 Saturday = G12 If you put the date (in single digit format) that each first day of the month falls on (i.e. if the first day of the week is Wednesday, G9=1, G10=2, G11=3, G12=4, G6=5, G7=6, G8=7), then the following fomulas work quite well for me: =IF(Sheet1!G6=1,SUM(Sheet1!D7*0.015),IF(Sheet1!G6= 2,SUM(Sheet1!D7*0.038),IF(Sheet1!G6=3,SUM(Sheet1!D 7*0.046),IF(Sheet1!G6=4,SUM(Sheet1!D7*0.051),IF(Sh eet1!G6=5,SUM(Sheet1!D7*0.045),IF(Sheet1!G6=6,SUM( Sheet1!D7*0.02),IF(Sheet1!G6=7,SUM(Sheet1!D7*0.018 )))))))) =IF(Sheet1!G7=2,SUM(Sheet1!D7*0.038),IF(Sheet1!G7= 3,SUM(Sheet1!D7*0.046),IF(Sheet1!G7=4,SUM(Sheet1!D 7*0.051),IF(Sheet1!G7=5,SUM(Sheet1!D7*0.045),IF(Sh eet1!G7=6,SUM(Sheet1!D7*0.02),IF(Sheet1!G7=7,SUM(S heet1!D7*0.018),IF(Sheet1!G7=1,SUM(Sheet1!D7*0.015 )))))))) =IF(Sheet1!G8=3,SUM(Sheet1!D7*0.046),IF(Sheet1!G8= 4,SUM(Sheet1!D7*0.051),IF(Sheet1!G8=5,SUM(Sheet1!D 7*0.045),IF(Sheet1!G8=6,SUM(Sheet1!D7*0.02),IF(She et1!G8=7,SUM(Sheet1!D7*0.018),IF(Sheet1!G8=1,SUM(S heet1!D7*0.015),IF(Sheet1!G8=2,SUM(Sheet1!D7*0.038 )))))))) =IF(Sheet1!G9=4,SUM(Sheet1!D7*0.051),IF(Sheet1!G9= 5,SUM(Sheet1!D7*0.045),IF(Sheet1!G9=6,SUM(Sheet1!D 7*0.02),IF(Sheet1!G9=7,SUM(Sheet1!D7*0.018),IF(She et1!G9=1,SUM(Sheet1!D7*0.015),IF(Sheet1!G9=2,SUM(S heet1!D7*0.038),IF(Sheet1!G9=3,SUM(Sheet1!D7*0.046 )))))))) =IF(Sheet1!G10=5,SUM(Sheet1!D7*0.045),IF(Sheet1!G1 0=6,SUM(Sheet1!D7*0.02),IF(Sheet1!G10=7,SUM(Sheet1 !D7*0.018),IF(Sheet1!G10=1,SUM(Sheet1!D7*0.015),IF (Sheet1!G10=2,SUM(Sheet1!D7*0.038),IF(Sheet1!G10=3 ,SUM(Sheet1!D7*0.046),IF(Sheet1!G10=4,SUM(Sheet1!D 7*0.051)))))))) =IF(Sheet1!G11=6,SUM(Sheet1!D7*0.02),IF(Sheet1!G11 =7,SUM(Sheet1!D7*0.018),IF(Sheet1!G11=1,SUM(Sheet1 !D7*0.015),IF(Sheet1!G11=2,SUM(Sheet1!D7*0.038),IF (Sheet1!G11=3,SUM(Sheet1!D7*0.046),IF(Sheet1!G11=4 ,SUM(Sheet1!D7*0.051),IF(Sheet1!G11=5,SUM(Sheet1!D 7*0.045)))))))) =IF(Sheet1!G12=7,SUM(Sheet1!D7*0.018),IF(Sheet1!G1 2=1,SUM(Sheet1!D7*0.015),IF(Sheet1!G12=2,SUM(Sheet 1!D7*0.038),IF(Sheet1!G12=3,SUM(Sheet1!D7*0.046),I F(Sheet1!G12=4,SUM(Sheet1!D7*0.051),IF(Sheet1!G12= 5,SUM(Sheet1!D7*0.045),IF(Sheet1!G12=6,SUM(Sheet1! D7*0.02)))))))) I realize I hadn't taken your advice, Toppers, but I believe I was at least inspired. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function or formula to convert "text" month to number of month? | Excel Discussion (Misc queries) | |||
Create Month Timetable on a worksheet different month each works | Excel Worksheet Functions | |||
Function or formula to convert "text" month to number of month? | Excel Discussion (Misc queries) | |||
Function or formula to convert "text" month to number of month | Excel Discussion (Misc queries) | |||
transfer cell $ amount to other sheet month-to-month without overc | Excel Discussion (Misc queries) |