L8 | B8 | O8 | P8 | R8 |

89 | 85 | 95 | 75 | BUY |

My scenario is simple i want to display text in cell R8 when L8>O8,"BUY",B8>O8,"BUY-F",L8< P8."SELL",B8< P8,"SELL-F".

For this i used function ie =IF(L8>O8,"BUY",IF(B8>O8,"BUY-F",IF(L8< P8."SELL",IF(B8< P8,"SELL-F","")))), Datas in cell L8 & B8,are real time data and in cell O8 &P8 are predefined data (manually feeded or fixed data) when i execute above function it is diplaying "BUY" instead of blank bcz data in L8 is less than O8, but this function is works correctly only when if all cells are have real time data or predefined data, above scenario is an example of one cell i want to apply it to whole sheet pls help me to resolve this issue

Hi All,

I wanted to be able to find the difference between two dates based on whether one criteria is the same as the criteria above it if so calculate the date difference between, if it doesnt then leave it and go to the next. Please find an example of my data set below:

e.g from my data set below - for customer id 1, row 4 would look up row 3 for customer id and because they match, it would calculate the date difference, same for row 3 to row 2, but row 2 doesnt have any data above it, it would automatically throw out a 0 against it.

A B C

Any help would be greatly appreciated.

TIA

]]>I wanted to be able to find the difference between two dates based on whether one criteria is the same as the criteria above it if so calculate the date difference between, if it doesnt then leave it and go to the next. Please find an example of my data set below:

e.g from my data set below - for customer id 1, row 4 would look up row 3 for customer id and because they match, it would calculate the date difference, same for row 3 to row 2, but row 2 doesnt have any data above it, it would automatically throw out a 0 against it.

A B C

Customer ID | Date | Amount |

1 | 01/01/2012 | 20 |

1 | 02/01/2015 | 20 |

1 | 03/01/2016 | 35 |

3 | 12/12/2015 | 65 |

3 | 13/12/2018 | 12 |

5 | 23/01/2005 | 5 |

5 | 15/05/2007 | 14 |

12 | 14/02/2004 | 13 |

12 | 15/02/2006 | 8 |

12 | 16/02/2009 | 16 |

12 | 17/02/2012 | 19 |

13 | 16/05/2013 | 100 |

13 | 17/05/2014 | 120 |

13 | 18/05/2015 | 100 |

Any help would be greatly appreciated.

TIA

Hi Everyone,

can you help me with my workbook? If I select an insurer in N2, I wanted to show the list with the specific insurer, names and comment in M9 to O14, however, looks like my formula is returning just one row.

attached is the workbook.

Thank you.

can you help me with my workbook? If I select an insurer in N2, I wanted to show the list with the specific insurer, names and comment in M9 to O14, however, looks like my formula is returning just one row.

attached is the workbook.

Thank you.

I have a drop down list at I7 which I want to drop data into J7 when an item is selected from the list. For some reason the formulas I tried wont work or I am doing something wrong that I cant see. the specs sheet below in columns A and B are the ones that are for I7. Column A is in the drop down list and Column b is for J7. What I need is when fresh yeast is selected only 1% is entered in J7. The same for the dry instant yeast = .03%

If someone could help I would appreciate it.

Thanks in advance GregT

Hello!

There are products that are identical by name and same by price but the products codes are different.

When seller sells products writes only the name of the product, the quantity and its price, because of the goods are different by code

and same in price it is impossible to determine which product is sold.

I determine the product code by date, price and name, but I can’t take the quantity into account dynamically.

Can you help me with advice how to dynamically change the quantity of a sold product?

Thank You

]]>There are products that are identical by name and same by price but the products codes are different.

When seller sells products writes only the name of the product, the quantity and its price, because of the goods are different by code

and same in price it is impossible to determine which product is sold.

I determine the product code by date, price and name, but I can’t take the quantity into account dynamically.

Can you help me with advice how to dynamically change the quantity of a sold product?

Thank You

Hey everyone!

I have this spreadsheet that track when I get into to work and when I leave. The "Diff" row is where I want to insert a formula that will take the total amount of time that I've worked in the day and subtract 8 and half hours from it. So for example, under "10/16" I want the formula to output -30 or -.5 as the Total hours worked that day was less than 8 and half (8 hours worked that day).

I thought this would be simple but apparently you can't show a negative value in Time format which is fine but when you use a Number format the number is between 0 and .9999 so I can't interpret how many hours or minutes I've worked less or extra in that day.

Feel like this is a simple solution but I'm blanking on this hard for some reason. Can anyone help?

Thanks in advance!

Timesheet.xlsx

I have this spreadsheet that track when I get into to work and when I leave. The "Diff" row is where I want to insert a formula that will take the total amount of time that I've worked in the day and subtract 8 and half hours from it. So for example, under "10/16" I want the formula to output -30 or -.5 as the Total hours worked that day was less than 8 and half (8 hours worked that day).

I thought this would be simple but apparently you can't show a negative value in Time format which is fine but when you use a Number format the number is between 0 and .9999 so I can't interpret how many hours or minutes I've worked less or extra in that day.

Feel like this is a simple solution but I'm blanking on this hard for some reason. Can anyone help?

Thanks in advance!

Timesheet.xlsx

Hi amazing excel formula minds,

I've been trying to extend my current formula to span about 11 different rosters (in another tab titled Roster!), but I havent had much luck extended the below:

=IFERROR(IF($C$7=Roster!$C$2,INDEX(Roster!$C:$C,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$D$2,INDEX(Roster!$D:$D,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$E$2,INDEX(Roster!$E:$E,MATCH(F$10,Roster!$B:$B,0)),""))),"")

Basically just need the top replicated 11 times (each index match is for 1 roster type names go across ie. $C$2, $D$2, $E$2 etc through to $M$2) and roster patterns associated to each roster name go down the list ie. $C:$C, $D:$D, $E:$E etc through to $M:$M, the match codes will remain the same throughout the formula - hope this makes sense.

I usually just copy and paste entire IF(Index, Match) formula to replicate, but I cant quite figure out the end part where the closing parenthesis goes

Any help would be amazing!

Thank you in advance

]]>I've been trying to extend my current formula to span about 11 different rosters (in another tab titled Roster!), but I havent had much luck extended the below:

=IFERROR(IF($C$7=Roster!$C$2,INDEX(Roster!$C:$C,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$D$2,INDEX(Roster!$D:$D,MATCH(F$10,Roster!$B:$B,0)),IF($C$7=Roster!$E$2,INDEX(Roster!$E:$E,MATCH(F$10,Roster!$B:$B,0)),""))),"")

Basically just need the top replicated 11 times (each index match is for 1 roster type names go across ie. $C$2, $D$2, $E$2 etc through to $M$2) and roster patterns associated to each roster name go down the list ie. $C:$C, $D:$D, $E:$E etc through to $M:$M, the match codes will remain the same throughout the formula - hope this makes sense.

I usually just copy and paste entire IF(Index, Match) formula to replicate, but I cant quite figure out the end part where the closing parenthesis goes

Any help would be amazing!

Thank you in advance

Hi,

I'm pretty good with Google Sheets and Excel but I've been trying to get a formula to work for the following situation for a few days now and just can't get it to work - any help would be great.

I have a google form which staff use to enter the following: Name, Branch, Day of the week, Profit and Week number

Once the data has been entered another worksheet in the same spreadsheet collates the information and gives me the total profit for each person each week. However I can only get the formula to display one person (the first person from the responses that matches the Branch and Week) so anyone else working in the same branch that week wont show.

this is the formula I'm using at the moment

=INDEX('Form responses 1'!$A$2:$C$3,MATCH($L$3&B7,'Form responses 1'!$C$2:$C$3&'Form responses 1'!$F$2:$F$3,0),2)

any help would be great

Thanks

Martin

]]>I'm pretty good with Google Sheets and Excel but I've been trying to get a formula to work for the following situation for a few days now and just can't get it to work - any help would be great.

I have a google form which staff use to enter the following: Name, Branch, Day of the week, Profit and Week number

Once the data has been entered another worksheet in the same spreadsheet collates the information and gives me the total profit for each person each week. However I can only get the formula to display one person (the first person from the responses that matches the Branch and Week) so anyone else working in the same branch that week wont show.

this is the formula I'm using at the moment

=INDEX('Form responses 1'!$A$2:$C$3,MATCH($L$3&B7,'Form responses 1'!$C$2:$C$3&'Form responses 1'!$F$2:$F$3,0),2)

any help would be great

Thanks

Martin

Hi,

I have got a task which I cant figure out. I want to build a prediction model if we should buy stocks or not. And by using an external parameter (demand from customer on the whole market in our area which we get daily) I want to predict if the stock price tomorrow goes up or down.

I have columns with:

Daily prices for last 10 months.

The price change per day in %

The price change per day in $

Customer Ratio (The variable Im suppose use to predict with)

I want excel by the end of each day to look at the daily %change and on the customer ratio, and give me a signal if I should "Buy / No buy" TOMORROW (not the same day since it just closed). We always sell after 24 hours if excel doesnt tell us otherwise.

a) Maximize the profit

b) Keep the number of "loss-days" to a minimum (I.e control the risk!)

b) What threshold should be used on the customer ratio? I.e when is it good to buy or not?

c) What should the %-change be?

For example...I want to be able to say:

Okey if the customer ratio is below 4 and the price went up 5$ yesterday, we should buy today.

I CANT FIGURE IT OUT!

I have got a task which I cant figure out. I want to build a prediction model if we should buy stocks or not. And by using an external parameter (demand from customer on the whole market in our area which we get daily) I want to predict if the stock price tomorrow goes up or down.

I have columns with:

Daily prices for last 10 months.

The price change per day in %

The price change per day in $

Customer Ratio (The variable Im suppose use to predict with)

I want excel by the end of each day to look at the daily %change and on the customer ratio, and give me a signal if I should "Buy / No buy" TOMORROW (not the same day since it just closed). We always sell after 24 hours if excel doesnt tell us otherwise.

a) Maximize the profit

b) Keep the number of "loss-days" to a minimum (I.e control the risk!)

b) What threshold should be used on the customer ratio? I.e when is it good to buy or not?

c) What should the %-change be?

For example...I want to be able to say:

Okey if the customer ratio is below 4 and the price went up 5$ yesterday, we should buy today.

I CANT FIGURE IT OUT!

I have a table that has like 15 rows. When I first started the table, I was only using 3 formulas in the row. Now on row 15, I have added 2 more formulas, so there are 5 cells in the row with formulas. However, when I hit tab to start a new row, it only brings down the original 3 formulas, not the new 2 formulas.

I have gone into Options and turned on "Fill formulas in tables to create calculated columns", but it still doesn't work.

This is happening on multiple spreadsheets and I am clueless what to do.

Thanks in advance.

]]>I have gone into Options and turned on "Fill formulas in tables to create calculated columns", but it still doesn't work.

This is happening on multiple spreadsheets and I am clueless what to do.

Thanks in advance.

I am having a problem with an inconsistent formula error, even though it’s not an inconsistent formula. I am uncertain if the formula is working or not working. On my spreadsheet the formula is entered into cells R415, S415, T415, U415, V415 and for every week thereafter. The formula is:

"=(R10/$DL$8*$DO$8)+(R15/$DL$13*$DO$13)+(R20/$DL$18*$DO$18)+(R25/$DL$23*$DO$23)+(R30/$DL$28*$DO$28)+(R35/$DL$33*$DO$33)+

(R40/$DL$38*$DO$38)+(R45/$DL$43*$DO$43)+(R50/$DL$48*$DO$48)+(R56/$DL$54*$DO$54)+(R61/$DL$59*$DO$59)+(R66/$DL$64*$DO$64)+

(R71/$DL$69*$DO$69)+(R76/$DL$74*$DO$74)+(R81/$DL$79*$DO$79)+(R86/$DL$84*$DO$84)+(R91/$DL$89*$DO$89)+(R97/$DL$95*$DO$95)+

(R102/$DL$100*$DO$100)+(R107/$DL$105*$DO$105)+(R113/$DL$111*$DO$111)+(R118/$DL$116*$DO$116)+(R123/$DL$121*$DO$121)+

(R129/$DL$127*$DO$127)+(R134/$DL$132*$DO$132)+(R139/$DL$137*$DO$137)+(R144/$DL$142*$DO$142)+(R149/$DL$147*$DO$147)+

(R153/$DL$151*$DO$151)+(R157/$DL$155*$DO$155)+(R161/$DL$159*$DO$159)+(R165/$DL$163*$DO$163)+(R169/$DL$167*$DO$167)+

(R173/$DL$171*$DO$171)+(R177/$DL$175*$DO$175)+(R181/$DL$179*$DO$179)+(R185/$DL$183*$DO$183)+(R189/$DL$187*$DO$187)+

(R193/$DL$191*$DO$191)+(R197/$DL$195*$DO$195)+(R201/$DL$199*$DO$199)+(R205/$DL$203*$DO$203)+(R209/$DL$207*$DO$207)+

(R213/$DL$211*$DO$211)+(R217/$DL$215*$DO$215)+(R221/$DL$219*$DO$219)+(R225/$DL$223*$DO$223)+(R229/$DL$227*$DO$227)+

(R233/$DL$231*$DO$231)+(R237/$DL$235*$DO$235)+(R241/$DL$239*$DO$239)+(R245/$DL$243*$DO$243)+(R249/$DL$247*$DO$247)+

(R253/$DL$251*$DO$251)+(R257/$DL$255*$DO$255)+(R261/$DL$259*$DO$259)+(R265/$DL$263*$DO$263)+(R269/$DL$267*$DO$267)+

(R273/$DL$271*$DO$271)+(R277/$DL$275*$DO$275)+(R281/$DL$279*$DO$279)+(R285/$DL$283*$DO$283)+(R289/$DL$287*$DO$287)+

(R293/$DL$291*$DO$291)+(R297/$DL$295*$DO$295)+(R301/$DL$299*$DO$299)+(R305/$DL$303*$DO$303)+(R309/$DL$307*$DO$307)+

(R313/$DL$311*$DO$311)+(R317/$DL$315*$DO$315)+(R321/$DL$319*$DO$319)+(R325/$DL$323*$DO$323)+(R329/$DL$327*$DO$327)+

(R333/$DL$331*$DO$331)+(R337/$DL$335*$DO$335)+(R341/$DL$339*$DO$339)+(R345/$DL$343*$DO$343)+(R349/$DL$347*$DO$347)+

(R353/$DL$351*$DO$351)+(R357/$DL$355*$DO$355)+(R361/$DL$359*$DO$359)+(R365/$DL$363*$DO$363)+(R369/$DL$367*$DO$367)+

(R373/$DL$371*$DO$371)+(R377/$DL$375*$DO$375)+(R381/$DL$379*$DO$379)+(R385/$DL$383*$DO$383)+(R389/$DL$387*$DO$387)+

(R393/$DL$391*$DO$391)+(R397/$DL$395*$DO$395)+(R401/$DL$399*$DO$399)+(R405/$DL$403*$DO$403)

The formula's pattern sometimes adds 5 rows (e.g. (R10/...)+(R15/...)+(R20/...) and it sometimes adds 4 rows (e.g. (R397/...)+(R401/...)+(R405/...), but it’s the same for all formulas in R415 to V415 and beyond. The inconsistent formula error has occurred since the spreadsheet was created, but the formula has still worked, until I added the most recent calculation. The problem I don’t understand is that when I added the last string "(R405/$DL$403*$DO$403), all of formula's tracing colors in the formula bar go black. It happens as soon as I type "/" for the last entry. Am I reaching a character limit or some kind of argument limit? Is the formula still working or has it stopped?

I have researched multiple online sources but have not found the answer yet. Does anyone know why Excel would turn the entire formula black after entering the most recent calculation into the formula, and has the formula stopped working?

]]>"=(R10/$DL$8*$DO$8)+(R15/$DL$13*$DO$13)+(R20/$DL$18*$DO$18)+(R25/$DL$23*$DO$23)+(R30/$DL$28*$DO$28)+(R35/$DL$33*$DO$33)+

(R40/$DL$38*$DO$38)+(R45/$DL$43*$DO$43)+(R50/$DL$48*$DO$48)+(R56/$DL$54*$DO$54)+(R61/$DL$59*$DO$59)+(R66/$DL$64*$DO$64)+

(R71/$DL$69*$DO$69)+(R76/$DL$74*$DO$74)+(R81/$DL$79*$DO$79)+(R86/$DL$84*$DO$84)+(R91/$DL$89*$DO$89)+(R97/$DL$95*$DO$95)+

(R102/$DL$100*$DO$100)+(R107/$DL$105*$DO$105)+(R113/$DL$111*$DO$111)+(R118/$DL$116*$DO$116)+(R123/$DL$121*$DO$121)+

(R129/$DL$127*$DO$127)+(R134/$DL$132*$DO$132)+(R139/$DL$137*$DO$137)+(R144/$DL$142*$DO$142)+(R149/$DL$147*$DO$147)+

(R153/$DL$151*$DO$151)+(R157/$DL$155*$DO$155)+(R161/$DL$159*$DO$159)+(R165/$DL$163*$DO$163)+(R169/$DL$167*$DO$167)+

(R173/$DL$171*$DO$171)+(R177/$DL$175*$DO$175)+(R181/$DL$179*$DO$179)+(R185/$DL$183*$DO$183)+(R189/$DL$187*$DO$187)+

(R193/$DL$191*$DO$191)+(R197/$DL$195*$DO$195)+(R201/$DL$199*$DO$199)+(R205/$DL$203*$DO$203)+(R209/$DL$207*$DO$207)+

(R213/$DL$211*$DO$211)+(R217/$DL$215*$DO$215)+(R221/$DL$219*$DO$219)+(R225/$DL$223*$DO$223)+(R229/$DL$227*$DO$227)+

(R233/$DL$231*$DO$231)+(R237/$DL$235*$DO$235)+(R241/$DL$239*$DO$239)+(R245/$DL$243*$DO$243)+(R249/$DL$247*$DO$247)+

(R253/$DL$251*$DO$251)+(R257/$DL$255*$DO$255)+(R261/$DL$259*$DO$259)+(R265/$DL$263*$DO$263)+(R269/$DL$267*$DO$267)+

(R273/$DL$271*$DO$271)+(R277/$DL$275*$DO$275)+(R281/$DL$279*$DO$279)+(R285/$DL$283*$DO$283)+(R289/$DL$287*$DO$287)+

(R293/$DL$291*$DO$291)+(R297/$DL$295*$DO$295)+(R301/$DL$299*$DO$299)+(R305/$DL$303*$DO$303)+(R309/$DL$307*$DO$307)+

(R313/$DL$311*$DO$311)+(R317/$DL$315*$DO$315)+(R321/$DL$319*$DO$319)+(R325/$DL$323*$DO$323)+(R329/$DL$327*$DO$327)+

(R333/$DL$331*$DO$331)+(R337/$DL$335*$DO$335)+(R341/$DL$339*$DO$339)+(R345/$DL$343*$DO$343)+(R349/$DL$347*$DO$347)+

(R353/$DL$351*$DO$351)+(R357/$DL$355*$DO$355)+(R361/$DL$359*$DO$359)+(R365/$DL$363*$DO$363)+(R369/$DL$367*$DO$367)+

(R373/$DL$371*$DO$371)+(R377/$DL$375*$DO$375)+(R381/$DL$379*$DO$379)+(R385/$DL$383*$DO$383)+(R389/$DL$387*$DO$387)+

(R393/$DL$391*$DO$391)+(R397/$DL$395*$DO$395)+(R401/$DL$399*$DO$399)+(R405/$DL$403*$DO$403)

The formula's pattern sometimes adds 5 rows (e.g. (R10/...)+(R15/...)+(R20/...) and it sometimes adds 4 rows (e.g. (R397/...)+(R401/...)+(R405/...), but it’s the same for all formulas in R415 to V415 and beyond. The inconsistent formula error has occurred since the spreadsheet was created, but the formula has still worked, until I added the most recent calculation. The problem I don’t understand is that when I added the last string "(R405/$DL$403*$DO$403), all of formula's tracing colors in the formula bar go black. It happens as soon as I type "/" for the last entry. Am I reaching a character limit or some kind of argument limit? Is the formula still working or has it stopped?

I have researched multiple online sources but have not found the answer yet. Does anyone know why Excel would turn the entire formula black after entering the most recent calculation into the formula, and has the formula stopped working?

I have about 17 files on the one drive, others go into these files and update them.

I download them daily and I have a master workbook on my computer that looks at these files when I download them. (This was working for months) now the master file does not update the values. When I go to edit links it says “Warning: source not recalculated”

The only way that I get this to work now is to open the file and re-save it after that it’ll work.

Sent from my iPhone using Tapatalk

]]>I download them daily and I have a master workbook on my computer that looks at these files when I download them. (This was working for months) now the master file does not update the values. When I go to edit links it says “Warning: source not recalculated”

The only way that I get this to work now is to open the file and re-save it after that it’ll work.

Sent from my iPhone using Tapatalk

Hi,

Can't attach the the file to the post as there is no option to do so which I need help with but this is where the file is located: https://trumpexcel.com/excel-leave-tracker/

What I want to change in this file is to include “IN” and “OFF” on the calendar days that employees are working and have off days but I do not want these words to be counted in the "Leaves This Month", "Leaves This Year" and "Leave Breakup" as leave days. So I want to use it as an work attendance tracker too. All 10 employees have different start days and off days. Which formulas do I need to change please to achieve the aforementioned?

Thank you very much in advance.

]]>Can't attach the the file to the post as there is no option to do so which I need help with but this is where the file is located: https://trumpexcel.com/excel-leave-tracker/

What I want to change in this file is to include “IN” and “OFF” on the calendar days that employees are working and have off days but I do not want these words to be counted in the "Leaves This Month", "Leaves This Year" and "Leave Breakup" as leave days. So I want to use it as an work attendance tracker too. All 10 employees have different start days and off days. Which formulas do I need to change please to achieve the aforementioned?

Thank you very much in advance.

Hello all,

Is there a formula to change the font color of the numbers to the left of the decimal point or vice versa? I want the the numbers to the left of the decimal point to stand out. I would like to see them lime green or bright blue while the numbers to the right of the decimal remain black. Thank you all in advance.

]]>Is there a formula to change the font color of the numbers to the left of the decimal point or vice versa? I want the the numbers to the left of the decimal point to stand out. I would like to see them lime green or bright blue while the numbers to the right of the decimal remain black. Thank you all in advance.