The reason is as the IF statements have grown, changed, copied -- they are not working as they should. And this is why Im looking for a better way. IF!!! I could do this in a db with a better front end - that would be great but - Im not sure this company will let me do that so for right now - small steps to at least fix all the problems this sheet is causing. As for specifically H and I, both of those items have had - as said in the original post - more possible values added to over time, and will each have 5 possible values in a few months so, the IF statement will be updated again and if someone put the wrong numbers in or it gets corrupted by the copying - Ill need to go back and fix that. Its a mess.
-- Registered Linux User: #480675 Registered Linux Machine: #408606 Linux since June 2005 On Mon, Oct 10, 2022 at 8:22 PM Michael D. Setzer II <msetze...@gmail.com> wrote: > > Looked at the spreadsheet and tried some things, but not clear on your > calculation. > Copied formula from first row down to row 16 then modified. > =IF(B16=1,6)+IF(B16=2,12) > +IF(C16=1,10)+IF(C16=2,20) > +IF(D16=1,16)+IF(D16=2,32) > +IF(E16=1,28)+IF(E16=2,56) > +IF(F16=1,38)+IF(F16=2,76) > +IF(G16=1,46)+IF(G16=2,92) > +IF(H16=1,60)+IF(H16=2,120)+IF(H16=3,180)+IF(H16=4,30) > +IF(I16=1,80)+IF(I16=2,160)+IF(I16=3,40) > Not clear why H16 being 4 give 30 where previous patterns would have it give > 240?? Same with I16 being 3 gives 40 instead of 240. > Came with this simple formula and it mached result in most cases. > =b16*6+c16*10+d16*16+e16*28+f16*38+g16*46*h16*60+i16*80 > Copied the relative formulas and numbers matched except where values in H or > I was 4 or 3. > Didn't look at vlookup formula. > Did note that on some of the lower rows you have blanks in cells that > resulted in #value, but chaning them to 0 resulted in correct numbers. > So, is there a reason why H and I being 4 or 3 give different values from the > pattern. Same thing with shifting to other blocks. > On 10 Oct 2022 at 18:40, Wade Smart wrote: > From: Wade Smart <wadesm...@gmail.com> > Date sent: Mon, 10 Oct 2022 18:40:55 -0500 > Subject: Re: [libreoffice-users] [more] Vlookup, and other Help > Copies to: users@global.libreoffice.org > > Well, that is odd. > > Im uploading again ... > > > > What about this link? > > https://drive.google.com/file/d/1-9vnC9UAoffLdXcVDTTN6oIJ8UJO041w/view?usp=sharing > > -- > > Registered Linux User: #480675 > > Registered Linux Machine: #408606 > > Linux since June 2005 > > > > On Mon, Oct 10, 2022 at 6:37 PM Steve Edmonds > > <steve.edmo...@ptglobal.com> wrote: > > > > > > At that link I get no download option and a message that the file is in > > > your trash. > > > Steve > > > > > > On 11/10/2022 12:27, Wade Smart wrote: > > > > https://drive.google.com/file/d/1Q4Skv003T-puY-2CBr-MMQHUVC-qBhfw/view?usp=sharing > > > > > > > > I thought I was making headway but just got myself into more of a > > > > problem. > > > > > > > > The link above is a sample of what Im working with. > > > > In this spread sheet I have hundreds of sections of this that are > > > > added to and updated. > > > > > > > > If you click on cell BG4 you'll see the IF hell function that I > > > > continually update (and fix). > > > > This is created by someone else and the IF statement doesnt account > > > > for all the things > > > > that are currently going on but - in general - Section One is B4 to > > > > I4. The entire colored > > > > space from B3 to I13 is the whole section. Each row is calculated by > > > > putting in 1 thru 4 under > > > > each item name, though at the moment, and the reason Im looking to do > > > > this [upgrade] is that > > > > 3 and 4 and the coming 5 are not being calculated. > > > > > > > > Row 4, B4 to I4 either a 1 or 2 is put in. J4 is the number of items. > > > > Cell BE4 totals all items from each section. > > > > > > > > Cell BG4 - currently the massive and incorrect IF statement - adds up > > > > each cell and calculates > > > > a number. In this example, F4 is BL SM and is a 1 so BG is 38. Each > > > > row can have a 1 thru 4 > > > > (upcoming 5) in each of the available cells. > > > > > > > > > > > > Looking at Row 7, Cell BG7 is the start of something I thought would > > > > work but... I got stuck. > > > > And, to be really honest, I got stuck writing this email for help > > > > because - I got lost in trying to > > > > explain this HAHA > > > > > > > > Looking at the function in BG7, check if its a number first and then > > > > look up the value of F3 item > > > > 1 in Sheet two which is 38. Cell BG8 does the same but shows the > > > > quickly growing IF statement > > > > for getting data for 2 cells. > > > > > > > > Question: does someone know of an easier way to do this without > > > > ongoing IF statements? > > > > > > > > Wade > > > > > > > > > > > > > > > > > > > > > -- > > > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > > > Problems? > > > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > > > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette > > > List archive: https://listarchives.libreoffice.org/global/users/ > > > Privacy Policy: https://www.documentfoundation.org/privacy > > > > -- > > To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org > > Problems? > > https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ > > Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette > > List archive: https://listarchives.libreoffice.org/global/users/ > > Privacy Policy: https://www.documentfoundation.org/privacy > +------------------------------------------------------------+ > Michael D. Setzer II - Computer Science Instructor (Retired) > mailto:mi...@guam.net > mailto:msetze...@gmail.com > Guam - Where America's Day Begins > G4L Disk Imaging Project maintainer > http://sourceforge.net/projects/g4l/ > +------------------------------------------------------------+ -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette List archive: https://listarchives.libreoffice.org/global/users/ Privacy Policy: https://www.documentfoundation.org/privacy
