Trying to get a Boolean formula to work month-to-month

[Guest]

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(Sheet1!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?

 

[Guest]

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?

 

[Guest]

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?

 

[Guest]

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.

 

[Guest]

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!D7*0.046),IF(Sheet1!G6=4,SUM(Sheet1!D7*0.051),IF(Sheet1!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!D7*0.051),IF(Sheet1!G7=5,SUM(Sheet1!D7*0.045),IF(Sheet1!G7=6,SUM(Sheet1!D7*0.02),IF(Sheet1!G7=7,SUM(Sheet1!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!D7*0.045),IF(Sheet1!G8=6,SUM(Sheet1!D7*0.02),IF(Sheet1!G8=7,SUM(Sheet1!D7*0.018),IF(Sheet1!G8=1,SUM(Sheet1!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!D7*0.02),IF(Sheet1!G9=7,SUM(Sheet1!D7*0.018),IF(Sheet1!G9=1,SUM(Sheet1!D7*0.015),IF(Sheet1!G9=2,SUM(Sheet1!D7*0.038),IF(Sheet1!G9=3,SUM(Sheet1!D7*0.046))))))))

=IF(Sheet1!G10=5,SUM(Sheet1!D7*0.045),IF(Sheet1!G10=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!D7*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!D7*0.045))))))))

=IF(Sheet1!G12=7,SUM(Sheet1!D7*0.018),IF(Sheet1!G12=1,SUM(Sheet1!D7*0.015),IF(Sheet1!G12=2,SUM(Sheet1!D7*0.038),IF(Sheet1!G12=3,SUM(Sheet1!D7*0.046),IF(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.