Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,339
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,339
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Function or formula to convert "text" month to number of month? Steve Vincent Excel Discussion (Misc queries) 5 May 15th 07 01:11 AM
Create Month Timetable on a worksheet different month each works Courtney Excel Worksheet Functions 1 October 15th 06 11:48 AM
Function or formula to convert "text" month to number of month? Kevin Vaughn Excel Discussion (Misc queries) 0 February 4th 06 05:45 PM
Function or formula to convert "text" month to number of month Steve Vincent Excel Discussion (Misc queries) 1 February 4th 06 05:19 PM
transfer cell $ amount to other sheet month-to-month without overc Colin2u Excel Discussion (Misc queries) 1 July 28th 05 02:36 AM


All times are GMT +1. The time now is 07:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"